Data Validation is one of Excel’s most underutilized tools from a data entry perspective, especially its ability to manage list type functions. What is Data Validation? Well it’s essentially a robust set of tools that allow you to define the parameters you want for certain data entry cells. In other words, you can control what users can & can’t enter in the cells that you define.
How many times do you have to enter some of the same information in a form, or run into situations where users butcher what should be normal entries, or they don’t enter data in a format you need? Have you ever wondered if you could come up with a simple solution that would make not only your life easier, but your users’ as well? With Data Validation you can fix almost all of that (although no matter what you do you can always count on a certain population to completely mess up what you’re trying to do).
NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.
The most common use of Data Validation is to populate a cell (or cells) with a list of pre-defined items. Starting with a fairly common example, let’s say you have an expense report with a cell for users to enter their office location. Instead of relying on manual entry (and errors), you can use Data Validation to create a list of locations for them to select instead (Figure 1). I like to add a “helper cell” either above or to the left of the data validation cell to point users to where they should be looking to enter data (in this case “Office Location”):
Setting up your list:
There are two methods of creating lists with Data Validation. The first is a manual method using the Data Validation dialog directly. This is preferable for smaller lists that won’t vary often, like “Yes/No/Maybe”, “Male/Female”, etc. To use this method, select the target cell (where you want the selection to be made), then goto the Data tab–>Data Validation (/avv). On the Settings tab, in the Allow box, select “List”, then in the Source box enter your values, separated by commas, but with no spaces in between. E.G. “Yes,No,Maybe” (Figure 2).
As you can see, this is fairly easy for a short list, but can become cumbersome for larger lists, especially as the nature of the source dialog box is to allow you to select a range on the worksheet, so if you try to use the Home, End or Arrow keys when you’re in the Source dialog box, all of a sudden you’ll find a cell range entered into your list items, and you’ll have to delete it in order for the list to be valid (Figure 2.1). This means that you have to manually select each item with the mouse if you want to replace it, or retype the list.
Note that the two check boxes you see (Ignore blank and In-cell dropdown) will be checked as their default positions, and in general when working with lists you won’t need or want to uncheck them.
The second method for creating lists is to 1) enter the list in the same worksheet and refer to the range it occupies (Figure 3):
Or 2) the method I prefer is to use an unused worksheet (which can be hidden) and create a named range for the list. Enter your list on the sheet and goto the Formula tab–>Name–>Define (Figure 4):
Now you can go back to the Data Validation dialog and refer to the named range (Figure 5):
And you’ll get the same results as if you referred to a worksheet range (Figure 6):
Note: it’s not at all uncommon to forget the “=” when using named range references, but you’ll soon find out you forgot it when you select your data validated cell and your option is “MyList”. All you need to do is open the Data Validation dialog again and add the “=” to fix it.
The nice thing about using named ranges is that you can keep your lists away from your users, by adding a sheet for them, then hiding the sheet with goto the Home tab–>Cells–>Format–>Visibility–>Hide & Unhide). This is also a handy method if you need to maintain data sources hidden from users, like LOOKUP tables and other references.
User Input Message(s):
The second tab on the Data Validation Dialog is the Input Message, which will pop up a comment box on the target cell when it’s selected:
This is probably the easiest part of setting up Data Validation and you set it up through the Input Message tab:
There are two elements to the Input Message, the Title and the Message itself, both of which are pretty self explanatory. Note that there’s an option to show the message when the Data Validated cell is selected, which is pretty useless, as you don’t have to enter anything in there in the first place.
Error Alert Message(s):
The third tab on the Data Validation Dialog is the Error Alert Message, which will pop up a message box anytime a user tries to enter their own value instead of one that you have defined.
The entry for this is very similar to the Input Message:
The same goes for the check box to enable the Error Alert message check box as with the Input Message. If you don’t want to use those features, there’s not really a reason to check the box.
That’s pretty much it for the List feature in Data Validation, but I’ll be adding more stuff about Data Validation soon. If there’s anything you think I forgot in this post, or if you have any questions, please don’t hesitate to ask.