Automating Data Entry with Data Validation Lists
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.
Setting up Data Validation Lists
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):
Named range as a list source
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.
Smitty
I’m trying to set up a sign in sheet for staff members. I need to put the day and date at the top with each staff member’s name listed down the left side of the sheet. There will be one sign in sheet per weekday.
How do I set up Excel to automatically change the date?
Thank you
Sorry for taking so long to get back to you. If this is something you print out weekly, you can probably use =TODAY() for the first date, then add to it for each additional day with =TODAY()+1, or =Sheet1!A1+1 if you want a sheet reference.
Noting that those will be dynamic dates, so if you want static dates you’ll need to use VBA.
Hi,
Loved the method 2. I’ve created the drop down and done all that is required for displaying an error message. However, i can still enter values that are not present in the list and the error message pop up does not appear as well.
Also.. I am trying to automate this procedure as my list will keep changing. What would you suggest is the best way to do it? Is it better to create a macro and then run the macro every time I want to do it or will I have to write a vb script and source the list in it every time?
You need to make sure that “Show error alert after invalid data is entered” is checked on the Error Alert tab.
Nope, just use a named range. Then anytime you make a change in the range it will automatically update in Data Validation.
Hi,
The “Show error alert after invalid data is entered” is checked. Is this a bug or is there some other issue?
Hi I’ve managed to create a project that automatically opens and excel file then at a pre defined time execute a macro. about half way through i get a message box that asks if i want to replace the contents of a cell. Is there a way i can automate a yes click on this box???
I’m not sure. You can e-mail me the file and I’ll take a look. (Sorry for the late reply - I’ve been building a new PC).
You can use:
Application.DisplayAlerts = False
Which will suppress those messages. Just make sure to set it back to True at the end of your code because the VBE won’t reset it.
Sory for taking so long to respond, but I just got a new PC up and running after my old one died.
Can you create multiple choice using a validation list?
What do you mean by multiple choice? Can you clarify? You can have multiple options in a validation list, but I need a better idea of what you’re trying to accomplish.