Excel Basics: How to add drop down list to validate data
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:
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Dashboarding Fun – Display Smileys in your excel dashboards||5 Infographics that can WOW you [Aug 7] »|