Sep 24 2007

Create a Drop Down Menu in a Cell

I recently received an email from a reader asking how to add a hidden drop down menu inside a cell. I was unable to respond back to the reader (email address was invalid), but I thought it was a great question and wanted to add it to Excel Hints.  In just a couple steps, you’ll be able to add this feature to your spreadsheet and improve the validity of many spreadsheets you create.

[ad#in-post-ad]

How To Add the Hidden Drop Down Menu
Unlike using a drop down menu from the forms toolbar (there will eventually be a post about that), this drop down menu is not seen until that particular cell is selected.  Within your particular form you are creating, select the cell where you like the drop down menu to appear.  After selecting the cell, select Data–>Validation and a pop up box will appear ingeniously called “Data Validation”.

Under the ‘Settings’ Tab, select ‘list’ under the allow pull-down menu. Then under Source either select the range of cell you want included in the drop down menu, or type in the value with a comma to separate them.  When you are finished entering the range or numbers, hit OK.  That’s it.  Check out the screenshots below which will walk you through the steps.

In Cell Drop Down Menu Example
In this example, we have created a form where the user types in their personal information, but instead of entering an age, we want them to select an age range.  This is where an in cell drop down menu can come in handy.

Drop Down Menu 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. Select the cell where you want to place the drop down menu.

Drop Down Menu 2

 

 

 

 

 

 

 

 

 

 

 

 

2. Select Data–>Validation from the top menu.  The box that pops up should look like this.

Drop Down Menu 3

 

 

 

 

 

 

 

 

 

 

 

 

3. Select “list” from the Allow Menu, and enter (or select) the strings you want in your drop down menu.

Drop Down Menu 4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4. You should now see the drop down menu you have created with the options you entered in step 3.

Drop Down Menu 5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. You’re done.  You can now use the form you just created.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

9 Comments on this post

Trackbacks

  1. DROP DOWN MENU | Materials Find wrote:

    [...] drop down menu excelhints.com [...]

    July 27th, 2011 at 9:34 pm
  2. Datavisualisatie Berlijn : I06B wrote:
    January 22nd, 2012 at 9:14 am
  1. dawn said:

    this was a great help have spent ages trying to figure this out.
    thank you
    dawn

    January 7th, 2008 at 5:30 am
  2. fred said:

    I am looking for a way to color the background of the dropdown lists when I add them to a cell. For example…I create the dropdown list by utilizing the data validation method. Let’s say the first box in the drop down list is (a) the second is (b) and so on. I want (a) to be red, (b) to be green etc… I’ve tried coloring the boxes prior to selecting them but to no avail. When I click on the box and try to color it, the entire list is changed to the same color… Please help me.

    January 10th, 2008 at 3:17 am
  3. Anbareen Obied said:

    Very helpfull and answered my doubts.

    February 15th, 2008 at 10:37 am
  4. Alexander said:

    Very nice information! Saved a lot of time.
    A question: how do I attribute values to the cell, to show them on a graph?

    For instance, if I have 5 drop down items, and they are not numbers, they are phrases, how do I give them a value so that I can do a score on a graph?

    February 19th, 2008 at 1:49 pm
  5. stick said:

    good way to create a drop down, i use options that can change on a frequent basis. typing the list off to the side of the sheet in a series of cells, and then selecting those cells in the source box works good if you need dropdown options to change.

    May 15th, 2008 at 10:17 am
  6. john said:

    Good suggestion Stick.

    I need to fix this page as well, the formatting is way off!

    August 14th, 2008 at 10:07 am
  7. Jeff Evans said:

    Hi

    Your help in creating a ‘Drop Down Menu in a Cell’ was most helpful, but is there any way that you can jump through the list quickly as I have a list that is in alphabetical order and would like to be able to say press the letter ‘M’ and see the first group that begins with ‘M’ or if I start typing it brings up the possibilities (like it would normally happen in Excel, if there was already an instance of that word in the same column and attempts to autofill it for you).

    Hope this makes sense and thanks for the tips they are most helpful

    November 30th, 2009 at 10:13 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives