Tips for Excel, Word, PowerPoint and Other Applications
Data Validation in Excel
Why It Matters To You
Have you ever wondered how you can get nifty drop down boxes like this survey question?
The answer is Data Validation. Data Validation (Main Menu:Data:Validation or ALT D, L), lets you restrict and control the types of data entered into a cell.
Data Validation is probably the most useful when you are giving models or forms out to someone else to provide data (e.g., surveys, RFPs, etc). You may ask a very straight forward "yes/no" or "score from 1-5" question, but the answer always seems to come back in all flavors of yes, no, TBD, long-winded comments, etc. This can be problematic if your worksheet is set up to evaluate pure Yes or No answers. Comments, TBDs, etc won't count or sum properly.
How To ...
Data validation provides a controlled list of values to choose from. The cell value used in the calculation will be the list selection made by the user. To use data validation in a cell, go to Main Menu:Data:Validation and you should see the following text box.
If you click on the Allow dropdown, you will see the different ways you can control inputs.
Let's set up a simple list which allows the user to enter a score of 1-5. Select List. This will let you set up a custom list of up to 39 values. Enter your values, separating each with a comma. You can also link to a range of cells. Doing this allows you to dynamically change the drop down options by simply changing the linked cells. However, Data Validation only allows you to link to cells on the same worksheet.
Click OK. You should have a cell that presents a drop down that allows the user to select from the discrete values of 1, 2, 3, 4 or 5. If you want to spice it up, you can use the Input Message to provide instructions and/or the Error Alert to warn the user of an invalid answer. It's that simple.
If you have several similar Data Validation cells, and need to make changes, you can edit them all at the same time by checking the "Apply these changes to all …". Play around a bit with this and see what else it can do.
Effective Combinations with Data Validation
Controlling an input is only effective if you're going to use the input in a structure manner. If I don't care what format a vendor enters revenue figures, then why force them into a specific format. However, obtaining data in a specific and structured manner, allows you control how the data received is processed through your models.
- Use with any summary math function. Controlling the inputs allows you to ensure summary functions (COUNTIF, AVERAGE, SUMIF) all work properly and include all responses.
- Use with formatting standards. The drop down makes it easy to select an answer, but if I train my users to look for the blue cells (my standard for an input cell), then it's super easy.
- Use with VLOOKUP/HLOOKUP. I can use data validation to control inputs for a VLOOKUP. For example, if I had a table of spend by month, I can use the VLOOKUP any particular month's worth of data and combine it with a Data Validation to cell to ensure my user can only pick an allowable value (e.g., Jan, Feb, Mar ....)
Notes
| Last updated | 9/8/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | AutoFiltering in Excel |