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

Posted on August 7th, 2008 in Learn Excel - 36 comments

adding-drop-down-box-excel-howtoValidating 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. In-cell drop-down lists or combo boxes 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.

  1. Select the cell where you want validation, go to Data > Validation

    excel-data-validation-menu-option

  2. Specify the validation criteria

    validate-cells-based-on-lists

    In the data validation dialog box, select “list” for type of content to allow in the cell (as you can see, you can also validate data based on other conditions like length of text, data type, formula results, more on this in another post)

    In the source field start typing valid values separated by comma, for eg. if you want to allow only first 6 months to be entered in the field, type January, February, March, April, May, June

  3. See the validation in action

    When you press ok, your cell will now look like this.
    excel-cell-data-combo-box-entry

  4. Doing more: Fetching the validation list from another range of cells

    Instead of entering all the values that can go in to the cell in the data validation source field, you can maintain the data in the spreadsheet and refer to in the validation criteria. See below:

    custom-list-based-data-validation-excel-microsoft

More Excel Tips: How you can copy validation criteria from one cell to another, Master IF functions in Excel – 5 tips, 15 fun things you can do with excel now

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

36 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

Leave a Reply