Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to guide to get you started on data validation in excel.
Howto set up Drop Down list in Excel?
- First, set up a list of valid values in range of cells. Say your valid list of entries is in A1:A6.
- Now go the cell where you want to validation drop down to appear.
- Go to Data ribbon and click on Validation
- Set up “List” as allowed values and enter =A1:A6 as Source (see below picture)
- Done. Now you can see the drop-down in your cell.
Video – How to create Drop Down List in Excel & Best Practice Tips
I made a video with a real world example of drop down lists. Please watch it below to understand the set up process & how to customize the validation rules.
Sample workbook to Practice Data Validation Drop Downs
Click here to download the sample workbook to practice data validation drop down lists.
How-to ignore duplicates while setting up validation list?
If you want to use a source list that has duplicates and want to ignore them when setting up validation drop-down, then you have two options. Something like this:
- Use Excel Dynamic Arrays (works in Excel 365)
- Use Pivot Tables (works in all versions of Excel)
Let’s say your list of inputs is in customers[Education] column.
Using Dynamic Arrays
Note: This works only in Excel 365 with dynamic array feature. Not all 365 users will have access to DA now, but everyone of them will get Dynamic Arrays soon.
Just go to an empty cell (preferably in a separate worksheet like settings tab) and type =SORT(UNIQUE(customers[Education]))
Excel will spill your data down to next few cells depending on how many unique values are in your data.
Let’s say your formula is in cell A1
Now, go to Formulas > Define Name and create a name for validation options as,
Finally, use myOptions as list source for data validation.
Using Pivot Tables for drop down without duplicates
This is most compatible option as it works in all versions of Excel.
- In a new sheet or blank range, insert a pivot table from your data.
- Add the Education field to row labels area
- Remove any grand, sub-totals
- Let’s say the first item in the pivot is in cell A2.
Now, create a name with myOptions and use the formula
=OFFSET($A$2,0,0, COUNTA($A$2:$A$21), 1)
this will make a dynamic named range with how many ever education options are there in that pivot table.
Note: Change $A$21 to a cell address further down if you will have more options.
Finally, use myOptions as the list source for data validation.
Your drop-down list without duplicates will be ready.
Drop-down list without duplicates – Video
I made a video explaining how to make dropdowns without duplicate values. You can see a cameo from Nishanth (my son) in the video. Check it out if you want to understand how Dynamic Array method and Pivot Table method can be setup. Watch it below or visit my YouTube Channel.
Best Practice for Drop-downs
Drop-downs are very useful for data analysis, charting and reporting work. They are user friendly and easy to set up. That said, keep these ideas in mind when implementing them.
- Use named ranges: Instead of hard-coding cell addresses, use named ranges for setting up validation lists. This will also enable you to connect data validation list to table columns thru structural references.
- Source lists in a separate tab: Whenever possible, set up all your source lists in a separate tab. I call mine “Settings”. This will make any changes easy for you.
- Don’t have too many options: If your drop-down is having more than 50 options, consider two-level cascading drop-downs or some other way to gather inputs.
- Try Form controls or Slicers too: Drop-downs are great, but they are just one of the many ways to add interactive abilities to your workbooks. Consider form controls and Slicers too.
Download Practice Workbook
I made a workbook with simple and “avoiding duplicates” examples. Please download it here and practice to learn more about these techniques.
More Data Validation Tricks
Here is a collection of useful tricks and ideas with Data Validation. Check them out to learn more.
43 Responses to “Excel Basics: How to add drop down list to validate data”
Chandoo,
In step 4,make sure that the data cells from where data is fetched for the drop down, is far far away/hidden/font color same as background.
Jus' a cosmetic tip.
I create drop downs like this and thought there was a better way.
This fetching data does not seem impressive, what say?
-Nikhil
(May be a duplicate comment)
I like to use the first rows in the sheet for the drop down options and then hide the rows. This way all of the source data is located in one spot and easy to find.
I find this function very useful and apply it all the time.
Another simple approach can be to use the below function....
=INDIRECT("info!E2:E" & COUNTA(INDIRECT("Info!$E:$E")))
here Info is the sheet name, containing the values that you want in the drop down in the Column E, starting from 2 row onwards...
the only catch is.....if afterwards you add / remove a column in the Info sheet, you will be requried to update the above to use the correct referencing.
HTH
~Vijay~
@Nikhil: you are right, often I use another sheet to have the lists, just to be safe that no one meddles with those.
@Tony: yeah, this saves a ton of time when you are creating sheets to circulate to others..
@Vijay, thanks for that tip, it is useful if you work on lots of lists that also change the values. Welcome to PHD, hope you liked my site...
Like Chandoo, I am always using another sheet to store the input lists for reasons of security and stability.
You only have to keep in mind that references to other sheets in data validation lists are only possible by giving the cell range a name and refering to this name in the source field of the data validation dialogue.
Thanks Chandoo...
indeed i am delighted a see a website (blog) where ideas are shared freely like this...kudos to you for setting up the platform....
great sutff.....cheers
~Vijay~
[...] Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet. [...]
It would be more useful if it found entries "as you type". Similar to the awesome bar in Firefox.
First I want to say this website is awesome! I haven't spent this long on a site reading just about everything in a long time. I have a problem that after hours of web research I can't find the answer. I want to create a list from a range of cells that are filled in by an If() statement, If(A2="F",B2,"") Because I can't find a way to leave a blank cell from the If() the Ignore blank box in the validation window doesn't work. Is there a way around this? Maybe a formula in the list box that ignores the "" cells?
[...] can use excel features like data validation drop downs, shared workbooks to make the timesheet entry and management a [...]
[...] above template becomes very easy to manage with excel features like data validation, filters and tables (lists in 2003 and [...]
[...] how can we set up a simple data validation list that would not repeat customer names and shows them in sorted order like [...]
Hi Chandoo,
Again a very useful note but if this can be updated to include steps to make a combo box in simple English in excel 2007 as well
Thanks again
Fakhar
[...] Then I set up 2 cells, one where user would specify “region” and other where a comparison “year” can be selected. I have used data validation to control the valid inputs. [...]
[...] let us assume we will use cells A1:A12 for automatic rolling months. Select A1 and set up data validation list on it (so that users can only enter a valid month in that cell) and use “List” type as [...]
Hi Chandoo -
Love your website - I use it often for my Excel questions.
Regarding lists as described in the above example, how can I allow multiple selections in one cell? If I have a list of A, B, and C, is there a way to set it up so the user could select both A & B without removing validation on that one cell?
Thanks
Lisa
the website is awesome– I use it often for my Excel questions.
[...] Use data validation: Getting invalid data in to your model can be quite frustrating. Instead of addressing invalid data thru formulas, you should try to avoid them by using data validation for input / assumption worksheets. (tip: allow only a list of values in a cell) [...]
[...] Excel Basics – Add Drop Down List using Data Validations [...]
Hi
Thank you very much
[...] tricks used – incell dropdown boxes, text boxes with formulas, symbols, and chart [...]
wanted to add one more ponit here - when you name the range and then you want to reference it in the Source box, and you named your range APPLES, the format is
=APPLES
😉
Thanks for all helpful ideas?
[...] http://chandoo.org [...]
[...] How to add a Drop-down list in Excel? [...]
Hi,
I am looking for a validation which can restrict a call value to be text only (i.e should not be able to enter numbers or special characters)
Can some one help me on this.
Hi,
I have a reference list in an excel file. In another excel file, I want to have a validation that refers to this reference list in the other excel. Though I have used named range, i cannot use it in validation. It gives me error "You cannot reference to other worksheets". How can I do this ?
Hi Sujatha
I think it is a restriction of Excel (Office) 2003. I'm still on this version and I have the same problem. Interestingly, a colleague who is on a later version (2010 I think), can do this and can save the spreadsheet in 2003, but when I open it, it crashes Excel!!
Ian
[...] Adding data validation drop downs in Excel – Introduction & Examples [...]
Hi chandoo,
I am using a Drop down list.
And in cell reference, I want to refer other sheet's cell.
Pls help me how can I do it?
Regards,
Ganesh Gudla
The link I shared below, has the solution you are looking for. The other sheet factor. Right.
[...] How to create an in-cell drop-down box for entering values? [...]
If you only want to store a few values in the cell drop down you can just type the data directly into the source box separated by commas and therefore no need for a separate data sheet. I'm not sure there is a limit but may be difficult to change data if the data set was very large
Such As:
10, 20, 30, 40, 50, 60, 70, 80, 90, 100
Found another article in this blog. This too has explained it nicely with an example.
http://www.encodedna.com/2013/12/how-to-add-or-remove-a-dropdown-list-in-excel.htm
Now the question is, how can this be useful for excel operators. Because there is one flaw or may be a limitation.
If you copy and paste values from another field to the field with the list, it won' t validate.
Any solution will be appreciate.
Thank you
Hi there ,
helpful example ,
can you please also tell how can we change some other field on change of this drop down values ??
Hey Chandoo
There is a way to get the list to auto expand, unfortunately I can't recall the formula but I believe you had posted it a while back.
When you enter in a range eg A1:A5 as the list and then add a value in A6 it won't be displayed in the drop-down menu.
However you can enter a formula to go from A1 to the last cell used effectively making it auto expand.
Great post though!
Hi Chandoo,
Love your site. Is there a way to allow multi-select in the dropdown? Please advise.
Sincerely,
Maria
Hello Chandoo,
I have a problem in one particular Excel file (in Excel 2010).
After inserting a drop-down list into a cell (Data > Data validation > Allow>List, Source>(range of cells in the worksheet)), the drop-down list indicator does not appear and consequently in the cell there is no list to choose from.
I can do it in other Excel files and new ones and it works.
I might have pressed some wrong shortcuts since I was experimenting with keyboard shortcuts in the document.
What can the problem be? Do you have any idea, please?
Thanks in advance.
Hi,
Is there a way to lock the data validation so that people can't remove it but can still choose and enter a value from the drop down menu. I can protect the cells but this doesn't allow for people to enter values.
Thanks
Suppose i have duplicate names in a column and i wanted combobox to only pick distinct names from a column or area. How do i achieve without deleting duplicates?
Nice tip. Is there a way to create multi select drop down please.
Hi chandoo, can you please provide some vba codes and explain in web scrapping
Good tips! Thanks for helping me out