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

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

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

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

Specify the validation criteria

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

  • See the validation in action

    When you press ok, your cell will now look like this.

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

  • Do you want to be awesome in Excel?

    Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

    Source: m.chandoo.org

    Category: Forex

    Similar articles: