fbpx

Excel Basics: How to add drop down list to validate data

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Excel Dropdown lists - demo

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?

  1. First, set up a list of valid values in range of cells. Say your valid list of entries is in A1:A6.
  2. Now go the cell where you want to validation drop down to appear.
  3. Go to Data ribbon and click on Validation
  4. Set up “List” as allowed values and enter =A1:A6 as Source (see below picture)
  5. Done. Now you can see the drop-down in your cell.
Excel Data Validation - Drop-down settings
Data Validation Settings

Howto 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:

drop-down in Excel without duplicates
Data Validation drop-down without duplicates
  1. Use Excel Dynamic Arrays (works in Excel 365)
  2. 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,

Dynamic array approach to get drop-down list without duplicates
Use # to tell Excel you want the entire spill range for the name

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.

  1. In a new sheet or blank range, insert a pivot table from your data.
  2. Add the Education field to row labels area
  3. Remove any grand, sub-totals
  4. Let’s say the first item in the pivot is in cell A2.
Pivot table method for creating data validation dropdown without duplicates
Pivot table with list of education values

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

42 Responses to “Excel Basics: How to add drop down list to validate data”

  1. 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)

  2. Tony says:

    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.

  3. Vijay Shama says:

    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~

  4. Chandoo says:

    @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...

  5. Robert says:

    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.

  6. Vijay Sharma says:

    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~

  7. [...] 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. [...]

  8. skube says:

    It would be more useful if it found entries "as you type". Similar to the awesome bar in Firefox.

  9. Dan Schmidt says:

    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?

  10. [...] can use excel features like data validation drop downs, shared workbooks to make the timesheet entry and management a [...]

  11. [...] above template becomes very easy to manage with excel features like data validation, filters and tables (lists in 2003 and [...]

  12. [...] how can we set up a simple data validation list that would not repeat customer names and shows them in sorted order like [...]

  13. Fakhar says:

    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

  14. [...] 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. [...]

  15. [...] 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 [...]

  16. Lisa says:

    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

  17. Shihabudheen says:

    the website is awesome– I use it often for my Excel questions.

  18. [...] 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) [...]

  19. balamurugan says:

    Hi
    Thank you very much

  20. [...] tricks used – incell dropdown boxes, text boxes with formulas, symbols, and chart [...]

  21. Preston says:

    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

    😉

  22. Naren says:

    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.

  23. Sujatha says:

    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 ?

    • Ian says:

      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

  24. [...] Adding data validation drop downs in Excel – Introduction & Examples [...]

  25. ganesh gudla says:

    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 

  26. [...] How to create an in-cell drop-down box for entering values? [...]

  27. Dee says:

    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

  28. rimi says:

    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

  29. Hussain Akhtar Wahid says:

    Hi there ,

    helpful example ,

    can you please also tell how can we change some other field on change of this drop down values ??

  30. Chris says:

    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!

  31. Maria says:

    Hi Chandoo,
    Love your site. Is there a way to allow multi-select in the dropdown? Please advise.
    Sincerely,
    Maria

  32. Lucia says:

    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.

  33. Kelly says:

    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

    • David says:

      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?

  34. Akhilesh Shukla says:

    Nice tip. Is there a way to create multi select drop down please.

  35. Hi chandoo, can you please provide some vba codes and explain in web scrapping

Leave a Reply