Creating an Excel drop down list, or Data Validation List as they're formally known, in Excel enables you to control the value entered into a specific cell by limiting the choices to those on a pre-defined list.
Excel validates that the entry in the cell matches an item from your pre-defined list, or you can simply click on the down arrow to select from an item in the list.
Download the workbook and follow along.
How to create an Excel drop down list
| 1) You first need to define the items that will appear in your list. To do this simply list the options in any column in your worksheet, preferably with no blank spaces between rows and no duplicates. |  |
2) Select the cell or range of cells you want validated.
3) Go to the Data tab of the ribbon in the Data Tools section click on ‘Data Validation’ and select ‘Data Validation’ from the list. You’ll get a dialog box like this:
 |
4) From the ‘Allow:’ list choose ‘List’. Your dialog box should now look like this:
 |
Make sure ‘In-cell dropdown’ is checked.
5) In the source box enter the cell range that your list is in. Note: the cell range should be absolute. That way if you copy the data validation to any other cells the reference remains on your list. If you aren’t sure how to make the reference absolute, let Excel do it for you by clicking in the Source box, then with your mouse, highlight the range of cells containing your list on your worksheet. Excel will enter the range in the correct format.
6) On the ‘Input Message’ tab you can enter a message that will appear when the user selects the cell. This is to give the user guidance as to what you want them to do. For example, mine simply says, ‘Please Pick From List’. Note: if you enter a title it will also appear in the message, so I’ve left mine blank....in this case I think saying it once should be enough 😉
 |
 |
7) The last tab is ‘Error Alert’. Here you can define the message you want displayed if the user tries to enter something that isn’t on the list.
 |
There are three styles of Error Alert you can choose from. Stop, as you see above, Warning, or Information. Simply pick the one you want from the ‘Style’ list.
That's it. You're good to go.
Advanced Options
- Referencing drop down lists on another worksheet: you might like to keep all your reference data on a separate ‘Legends’ worksheet to keep your workbook tidy.
To do this, in the 'Source' field you will need to add the sheet name in front of the cell range of your list (with the sheet name enclosed in inverted commas and with an exclamation mark at the end).
For example, instead of my reference being =$C$1:$C$7 it would be =’Legends’!$C$1:$C$7. Unfortunately you have to type this into the Source box, you can’t use your mouse to select the sheet with the cells containing your list as described in step 5 above.
- Creating Dynamic Lists: These update when items are added or removed from your Source. Make sure you do this if your list is likely to change. There's no point doing more work than you need to.
In Excel 2007 onwards this is easy to do. Simply format your list as a Table and then give the Table a Named Range. To do this,
A. Highlight your list
B. From Insert tab, select Table, click OK.
C. Now give the table a Named Range. I’ll call mine ‘ListPrograms’.
D. Now in the 'Source' type in the name of your range. In my case =ListPrograms as we did in Step 5.
Please share the knowlege with your friends and colleagues on Twitter, Facebook, Google+, LinkedIn etc.
Peter Buyze says
Mynda, good tutorial, as usual with your tutorials 🙂
I have a table as well as a list for the drop-down on the same sheet, though separated by an empty column.
Both are Excel Tables. I followed you
I gave the drop-down list a Name, but when I then click on the arrow in a cell to choose from the list, I get presented with the Name only. Clicking on that just adds the Name to the cell.
So what am I doing wrong?
Catalin Bombea says
Hi Peter,
Put an = sign before the name, in the Source field. For a static list, you can manually type any comma separated text, like: Yes,No,Other to create a dropdown with those 3 values.
Cheers,
Peter Buyze says
Hi Catalin, thanks for your reply, and apologies for not answering yesterday but I was a bit tied up.
I tried what you suggested with the = sign, but Excel does not accept that, and displays a message:
We found a problem with this formula.
So I looked at this again & tried to use the drop-down list, converted to a table, but without naming it. That works.
Catalin Bombea says
Hi Peter,
Looks like the formula used in that name was wrong. You can always upload a sample file on our forum, without a file it’s hard to see what is wrong.
Catalin
Steve says
Hi Mynda,
Great lesson. I think I found another way to do dynamic drop down lists:
A. make the list
B. make the list a table
C. in the validation source, type =Tablename[fieldname]
Now whenever a row is added, the new entry automatically shows up in the list.
Mynda Treacy says
Hi Steve,
Almost, you can’t use Structured References in the data validation list source, but if you simply reference the table (with your mouse) it will behave dynmically. Alternatively you can create a named range which uses your Table’s Structured References and use that named range in your data validation list source.
Mynda
sara says
Hi. Great lesson. I have created a number of tables for my drop down lists and used named ranges. Initially this was great as every time I added a new row, the lists were there. However, now when I add a new row, when I get to my first 2 lists, they are not there, but all the other further down the row are??!!! I cant work out why as I have used the same structure for all the lists. Any suggestions?
cheers Sara
Mynda Treacy says
Hi Sara,
Glad you liked the lesson on Drop Down Lists.
As for your question; I’d have to see the file to know what the problem is. You can send it to my via our Help Desk.
Cheers,
Mynda
Tim Stacy says
Hi,
I have a list of vendors and products in tabular format (duplicate vendor name and duplicate products). I want the user to select the vendor from a data validation list and then the corresponding products associated with the vendor. The vendor list and product list are in column A and B respectively (side by side).
I don’t want to create a named range for every vendor/product combination. Is there a method to only show distinct vendors and their corresponding products (without VBA and without me going crazy?)
Many thanks,
Tim
Catalin Bombea says
Hi Tim,
You can create dependent data validation lists, you have examples here: excel-data-validation-with-dependent-lists
and here: excel-factor-19-dynamic-dependent-data-validation
Catalin
Anna says
Hey, I’ve downloaded the Workbook for this, and I noticed that this List data validation is NOT case-sensitive. In other words, using the Workbook’s example, a person can of course (not only) choose from drop down list the option of Bat Man, (but also) type in the cell Bat Man, (and also) type in the cell bat man (notice lower case) but that last one IS accepted, although bat man in lowercase form is NOT in the defined list.
How do we specify that so it IS case sensitive?
Or how do we use List and UPPER (or simply, more than one “checkpoint”) together for one cell as data validation?
Thanks,
Anna
Catalin Bombea says
Hi Anna,
Excel dropdowns are quite rigid, there are many missing facilities, like changing the font size, autocomplete functionality (this works only if upper cells contains the word you are typing, so it’s not a drop down functionality, it’s the normal excel behaviour). This is the built-in functionality of drop down lists, and cannot be changed.
The only way i know to make a drop down list case sensitive, is to type your list directly into the Source field in the data validation menu, comma separated: From Data Validation Menu, Allow: List, Source: YeS,No
You will be able to select YeS, and to type YeS, but if you type “yes” or “Yes”, this will not be accepted, so it’s case sensitive…
Catalin
Anna says
it worked! thanks Catalin. This is the best website ever.
Catalin Bombea says
You’re wellcome 🙂
Anna says
Hey Catalin,
This may be a stupid question but is there a way to have excel take an input such as “Kg” or “kg” or “kG” and auto-convert it to “KG”? It may not be a data validation function anymore?
Thanks!
Anna
Catalin Bombea says
There is no way that i know to autoconvert to uppercase, only VBA can do that…
Spencer Lui says
Is there a way I can just type the first or few characters in the validation cell and the drop down list will point to the data that I want?
Thank you and regards.
Mynda Treacy says
Hi Spencer,
No, unfortunately data validation lists don’t auto-complete.
Kind regards,
Mynda
Kevin Pombriant says
Using Debra Dalgleish’s Data Validation with Combo box you can have auto complete.
K.
Mynda Treacy says
Thanks Kevin. Do you have a link for that?
Ashraf says
Dear Mynda,
Hope you are having a pleasant day….
this lesson was very awesome, i like your way and i got all the point, but also we can can create Dynamic Drop list as the following:
– In the source box enter the criteria that your want like (Ashraf,Mynda,…..
Thank you
Ashraf
Catalin Bombea says
Hi Ashraf,
I’m glad you liked this lesson!
You are right, values can be entered directly in source field, separated by commas, your observation is very useful, thank you for this.
The only thing i must add to your method, is that the values must be typed without parenthesis.
Best Regards,
Catalin
jayaseelan says
one of the best tutorial, easy to understand i really like the way u explained each example.
Mynda Treacy says
Thanks, Jayaseelan 🙂
Purnima says
Hi Mynda,
Thanks for such great tips, this page is really helpful! I am stuck with a drop down list that has a lot of values just listed as REF!(maybe because I deleted values from a sheet the list was referenced from? I did not create the sheet and am pretty new at this). Is there a way I can get the REF! to go away so I have a cleaner drop down menu?
Regards,
Purnima.
Mynda Treacy says
Hi Purnima,
Thanks for your kind words. To remove the REF erros go to the data validation list source and remove them. To find the source select the cell containing the drop down list > Data tab of ribbon > Data Validation.
I hope that helps.
Kind regards,
Mynda.
Kellie says
I am trying to create a drop down that has pictures to select and then after that selection has been made, the next 2 columns automatically fill-in with text corresponding. I need to be able to repeat in multiple rows. Any ideas?
Mynda Treacy says
Hi Kellie,
Sorry, I’m not aware of being able to set up a data validation list that displays pictures instead of text.
Kind regards,
Mynda.
Eduard Hetzel says
Hi Mynda
I’m still enjoying you’re tips and tricks, so Keep on!
Now I have a small problem with my drop down lists:
On one of my tables the drop down arrows (the arrows to open the list next to the cell) do not show anymore??
It must be something stupid, but I can’t find out what it is.
You have any idea?
regards
Eduard
Mynda Treacy says
Hi Eduard,
Thanks for your kind words 🙂
If your drop down lists no longer display the down arrows then they’re no longer drop down lists. I would select the cell you’re expecting to contain a drop down list and open the Data Validation dialog box (Data tab of ribbon > Data Validation) and see if the validation criteria is set to your list. I suspect it’s no longer set to your list.
If that’s not the problem it’s probably best if you send me your file so I can take a look.
Kind regards,
Mynda.
Eduard Hetzel says
Hi Mynda
This is just to give details on the solution.
Since the file was an Excel 2003 template, an somehow had a bug in it. Mynda mentioned, that I try to save the file as an “Excel-Macro-Enabled-Template (*.xltm)”, and the working files as “Excel-Macro-Enabled-Workbooks (*.xlsm)”. The result was, that my drop downs work again, and I just hope that all my users of the file have upgraded to at least 2007 Excel level by now.
It is just great how we can work – I ask a question here in Switzerland during my lunch break, and before the break is finished Mynda sent me the possible solutions from the other side of the world.
Thank you and kind regards
Eduard
Mynda Treacy says
Hi Eduard,
Thanks for taking the time to give your update.
Cheers,
Mynda.
Kaveh says
Does anyone know how to create a drop down list on excel with “all that applly” functionality? Essentailly when more than one option could be chosen from the list.
Mynda Treacy says
Hi Kaveh,
You can do it with VBA. Here is a tutorial by Debra Dalgleish on selecting multiple items from a data validation list.
Kind regards,
Mynda.
Deb Morrissey says
I have a spreadsheet where the first column dropdown determines the second column dropdown but when the first column choice is changed the second column choice remains the same until the column is selected and changed. Is there a way to highlight and alert the user that the choice is no longer valid?
Ex –
A2 is vegetable B2 is carrot
you change A2 to fruit but B2 remains at carrot until you select it and change the selection.
Let’s say you are distracted and don’t change it, I would like it to be highlighted to show that it is invalid. Is there a way to do that?
Carlo Estopia says
Hi Deb,
You can use formula formatting here.
1 Go to Home Ribbon
2 Click Conditional Formatting
3 Click New Rules
4 “Use a formula to determine which cells to format”
5 Enter this Formula
=A2<>B2
6 Format your fill to a color you like
7 Click OK
8 In the Applies to box select the range where you want
your rules to apply
Read More: Conditional Formatting
Cheers,
CarloE
Ajay Jangral says
Hi.
I have created a list containing few name, it works well, but when I am trying to use that list as reference in another cell, only the name selected in first cell is displayed and the second cell will not show the complete dropdown list, it show a single name selected in the first cell. Please assist me how I can use that list it without copy & Past option.
Mynda Treacy says
Hi Ajay,
Are you saying you want to use the same list in another location? If so, just insert another data validation list, or copy and paste the original one to the new location.
If I’ve misunderstood please send me the file with specific information on what you’re tyring to do and where.
Kind regards,
Mynda.
Ajay Jangral says
Hi.
I have created a list, by the option cut and paste, it works fine. But I don’t want to do it in this way. I Want to use the reference of the list created for creating another list so that it will provide the same option, as with the case of cut and copy.
thanks.
Ajay.
Mynda Treacy says
Hi Ajay,
I’m not sure I fully understand, but perhaps Copy and Paste Special > Links is what you’re looking for?
Kind regards,
Mynda.
Brett says
I have a spreadsheet where the first column dropdown determines the second column dropdown but when the first column choice is changed the second column choice remains the same until the column is selected and changed. Is there a way to highlight and alert the user that the choice is no longer valid?
Ex –
A2 is vegetable B2 is carrot
you change A2 to fruit but B2 remains at carrot until you select it and change the selection.
This example was previously posted but Is there a way to blank out all selections in the dependent drop down list when opening the spreadsheet so there is no legacy selections?
Mynda Treacy says
Hi Brett,
You would have to use VBA to clear invalid selections upon opening the file.
You could use Conditional Formatting to highlight, or make the font white (so it’s not visible), if the second column is of the wrong category.
I hope that gives you some ideas. Let us know if you get stuck.
Kind regards,
Mynda
richard says
How can I set the number of lines in the drop-down list?
Carlo Estopia says
Hi Richard,
I didn’t quite get what you mean by ‘number of lines’.
So I’m guessing you want to ignore blanks or some kind of dynamic
data validations.
Dynamic Range/Dynamic Data Validations
Cheers,
CarloE
PS: Please clarify further about what you mean by ‘number of lines’. I have a hunch however that It can’t be
done. Please send your file , if any, to help desk.
paul says
Thanks a lot. you relly help
Carlo Estopia says
Hi Paul,
On behalf of Mynda and Philip,
You’re Welcome!
CHeers.
CarloE
PAUL says
Have you ever heard of the ability to have a Search Assist in an Excel dropdown list? Is there a formula that you know of?
Best regards,
Paul
Mynda Treacy says
Hi Paul,
I don’t know of a way you can search in a dropdown/data validation list but Jerry Beaucaire has some creative implementations of Data Validation at his site here.
Use the menu on the left of his site to look at the different posts.
I hope you find something you can work with there.
Kind regards,
Mynda.
RAVI XAVIER says
THANK YOU VERY MUCH SIR THIS USEFUL SHARING.
Mynda Treacy says
You’re welcome, Ravi 🙂
NeMarr says
Really helpful, but despite making pick list values absolute, the list of options diminishes by one as you travel down each row in the sheet- any idea how this can be rectified?
Mynda Treacy says
Hi Neil,
I don’t know why you’re having that problem. It doesn’t diminish by one for me. If you would like to send me your example file I’d be happy to take a look.
Kind regards,
Mynda.
Fredy says
Thanks so much! May I ask “What is the Keyboard short cut for activating a chart in Excel?”
Mynda Treacy says
Hi Fredy,
Sorry, I don’t know a shortcut for that. I know that to insert a chart you can use F11. Please let me know if you find it.
Cheers,
Mynda.
turab says
Good it was well written and explained. – Have already joined you member group to remain active and informative.
Mynda Treacy says
Thanks Turab 🙂