Microsoft optimize

How to add a drop-down list to an Excel cell

Drop-down lists can greatly facilitate data entry. Here's a look at how to use Excel's data validation feature to create handy lists within your worksheets.

In Access, you can limit user entries by forcing users to choose a value from a list control. Office applications use the same functionality in built-in drop-down lists. For instance, the Highlight and Font Color controls on most Formatting toolbars use this flexible tool. Simply click the small triangle to the right of the icon to display a list of choices.

You can create the same type of control for your users in an Excel sheet, but the process isn't intuitive. The option is in the Data Validation feature. Fortunately, once you know the feature exists, it's easy to implement. You need only two things: a list and a data entry cell. The following sheet shows a simple drop-down list in an Excel sheet.

june200712fig1rx.jpg

Users click the drop-down arrow to display a list of items from A1:A4. If a user tries to enter something that isn't in the list, Excel rejects the entry. To add this drop-down list to a sheet, do the following:

  1. Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.
  2. Select cell E3. (You can position the drop-down list in most any cell or even multiple cells.)
  3. Choose Validation from the Data menu.
  4. Choose List from the Allow option's drop-down list. (See, they're everywhere.)
  5. Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).
  6. Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won't present a drop-down list.
  7. Click OK.

You can add the drop-down list to multiple cells. Select the range of data input cells (step 2) instead of a single cell. It even works for noncontiguous cells. Hold down the Shift key while you click the appropriate cells.

It's worth noting that the drop-down arrow is visible only when the cell is active.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

44 comments
Kyaz
Kyaz

I created my list from which I will pick multiple different ones for each cell. For example, tasks in cells and a list of personnel folks to complete the tasks. How do I pick a multiple personnel not in sequential order? I was rearranging the list to validate and as my list was reformatted so were the people in my drop down menu.

Ihaveaquestion
Ihaveaquestion

I would like to include a blank cell within the drop down list with the other selections offered. For example: black, white, red, (blank). Can anyone assist me on how to include a blank cell (empty cell).

ARTisticMarketing
ARTisticMarketing

Hello, Thank you for this wonderful topic. I needed to know how to do this, but I also need to know if I can insert this into my website? I was told I could insert the drop-box feature, as html? Can anyone help me with this?

Davsr
Davsr

This worked Great, to the point and I was able to Zoom through the process. It is great fro customers also. If choices do not change often, you can add as many as you need and change them quickly with out recreating the formula. Thank you.

tetolservices
tetolservices

Is it possible to add dro down list in a worksheet and use that list to filter records in the worksheet.

peni.qauqau
peni.qauqau

Can I have a concatenated drop down list consisting of 2 lists side by side. eg A/c code and its corresponding description. In addition if I selected from a list eg description, could I have the corresponding a/c code show? e.g Select Donation and A/c code 120 is displayed.

techrepublic
techrepublic

Can you make the cell picking up the values keep the same formatting as in the original list, e.g if a word is underlined in the original list, can it keep that underlining in the list cell??

namoebie
namoebie

Great, very usefull. But: How can the dropbox be widend so longer words can be fully displayed; and how can I enlarge the font?

JohnH5054
JohnH5054

Thank you very much. This is a time-saver for us !!

graham
graham

I've been using drop downs in Excel for years, I didn't think it was such a secret. The irritating thing about data validation is that it does not support auto-fill (type-ahead). One would hope and expect that as you start to type in the cell it would jump to the most similar word on the list and offer that, as an alternative to clicking on the drop down arrow. Most times auto-fill would be much quicker, especially if you know most of the options on your list. Mine (Office XP) doesn't do that, so if anyone can tell me how, then that WOULD be a breakthrough.

daniel
daniel

I do not know if it was mentionned but you should sort the original list at some point. This way the items will appear in ascending order. Typing each item manually (separated by a comma) is hard to manage. Definitely the best way to do this is by typing your list in a separate sheet then highlight it and type a name in the Name box (for example State).Finally sort the list in ascending order. Once this is done in the Validation Source hit the F3 key to list all the range names and select the correct one. If you need to add a new entry insert a cell within the original list (not at the end), type the new entry then sort it again. Everywhere where the list exist in the workbook will be automatically updated. Cheers

hariks0
hariks0

If somebody wants to have a different list for different values in a given cell, this method could be extended further. The list will contain book names instead of animals and birds as in the screenshot.

coveycraig
coveycraig

You made an error. "1.Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D4." The range for your row example should be "A1:D1".

chrischilds
chrischilds

How do i create dependant drop down lists. I have looked at using the Inderict() but i can not understand how to get it to work for me. I have a list of 46 catagories on one sheet with a key next to them. on another sheet i have all the sub catagories listed in two colums; one column listing a key to the main catagory with 5 descriptions listed in the second column for each catagory. From what I have tried the indirect does not work as it looks at subcatagories that are indiviual columns.

d_a_t_101
d_a_t_101

You can also just type in the criteria box (after you have selected the Validation option) just separate the option with a comma

tallen
tallen

How do I remove a drop down list from a cell?

thanawat.kan
thanawat.kan

How to add color in dropdown list? For example cat is red dog is blue horse is green and bird is on white

chris.yates
chris.yates

Is there a maximum list length. I cannot extend my cell list beyond 195 drop down entities

dkent
dkent

How do I make the drop down box visible even when it is not selected

kapasi4u
kapasi4u

Thanks a lot man!!! This helped me a lot.....Cheers !!

temujaya
temujaya

This article is useful. But, i wanted to know that 'How to add additional data to existing dropdown list in excel' For. Ex. here we have selected data from A1:A4, now i want to add 2 more values in A5 and A6. Please let me know how this can be done to get the same list with 2 new values

walterknecht
walterknecht

I can do this/have done this. The drop down seems to stop at about 750 records. My list is longer (about 800) and properly refernced but the drop down willnot let me scroll to the real bottom? Ideas on how to get all the way?

doco42
doco42

What about a drop down list of hyperlinks??

dogknees
dogknees

If you're using this tip, there are a few things to be aware of. You don't need to setup the list of options on a worksheet, you can just type the items into the Source box on the Data Validation dialog. ie Enter Yes,No,Don't Know to get these options. If you are using a list on a worksheet, but don't want to put it on the same worksheet as the validated cells, name the range your using (Insert/Name/Define) then use that name when you enter the Source. ie If you range is named "Choices", enter "=Choices". The Validation dialog won't allow direct references to other sheets, but using the named range gets around the limitiation. Hope these help.

KrishnaPG
KrishnaPG

In the data validation dialog, check "ignore blank" - that way users can delete any typed content in that cell and leave it empty.

cozy
cozy

Using the same method for creating the drop down list select "Any value" from the Validation Criteria "Allow" list. This removes the drop down button as there is no predetermied data set to pick from.

michael.cutler
michael.cutler

Building on the suggestion of the named range, use a dynamic named range and you won't have to adjust the range parameters when you add new items.

Solarfish
Solarfish

Instead of hard coding the range of cells for the list, if you use a named range then you can increase the length of the range without having to change your validation settings.

ssharkins
ssharkins

I might not understand your question. If you add items to the sheet, you must recreate the list. There's probably a way to update the list via a macro, but unless you do it often, it's simpler just to recreate it.

Lost Cause?
Lost Cause?

I ran into a problem similar to this. I ended up using the VLOOKUP function to solve my issue.

ssharkins
ssharkins

I'm sorry, but I don't know of anyway to maintain the link in a dropdown list. The control will display them, but they won't be live links.

dhivya.vasu01
dhivya.vasu01

Thankyou . the above post was really helpful. i have another doubt .. i have added the dropdown in the field required . now i need to know how to add another dropdown relating to the 1st dropdown box that i already added . like for e.g., A1 have the drop down stating countries list .it have some 50 countries. now i need next column B1 to have states for the simultaneous country. Say if i select India in A1 i should get options only for states in India (i.e its should have only the states in india . i should not have any states in other countries).. and if i select other countries i should have option to select states only from that particular country. could you please reply .. thank you

hal001
hal001

Thanks for the note about naming the range of data on another sheet. It works.

kdavis
kdavis

I have a spreadsheet I have to produce and update twice a week. I thought the first hint was really good, but with your information, I can take the cell and copy it from sheet to sheet! You just eliminated about 20 minutes a week of data entry for me.

ssharkins
ssharkins

The feature truly is very flexible. Thanks of the additional information. I had planned to follow up with an entry on how to work with lists not in the current sheet, but now I don't have to! :)

Amnezia
Amnezia

Once the required link is in the cell, clicking on another cell makes the inserted link live - then clicking the link opens your browser. But the list of hyperlinks must be somewhere on the same sheet. What I don't know is how to get the data validation part to link to the other sheet holding the hyperlink list. Perhaps entering the data link manually would be possible, or use a VLOOKUP in the parent cell linking to the list sheet and cell refs or named range.