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