You could write a book about all the ways you can use Excel’s Data Validation feature. Like its name suggests, this feature provides tools you need to validate entries made in a spreadsheet.
Instead of letting an end user have carte blanche to enter anything at all into a range of cells, Data Validation lets you set up rules to “kick out” any entry that either doesn’t appear in a master list of “valid” entries or doesn’t meet specific conditions. And for your data entry clerks who’d rather click than type, Data Validation lets you create drop-down lists from which users can select an entry from the master list.
If you want to allow for occasional exceptions to your validation rules, you can tell Excel simply to display a custom warning message (instead of kicking out the entry) when someone enters a value that isn’t on the master list. Here’s a quick introductory lesson to get your power users started with this valuable tool.
Summary of the technique
First, type a list of acceptable entries for a given column and assign a range name to those cells. Then, select the range of cells whose entries you want to validate during data entry and use the Data menu’s Validation option to “point” to the named range of acceptable entries. When you set up the validation rule, you can decide whether to allow exceptions or to enforce the rule at all times.
Sample case: Restricting entries to a list of names
Although I can’t demonstrate every possible use of the Data Validation feature in this short article, working through this example will help you start off on the right foot.
Let’s suppose you’re setting up a simple spreadsheet for a clerk’s use in entering sales transactions. Your company only employs four sales agents, and you want to set up this simple validation rule for the data: In the Agent column, the clerk must enter one of those four names: Barney, Betty, Fred, or Wilma—no other names, and no blanks allowed.
Step 1: Type a list of entries and name that range
To begin, simply type those names in a column anywhere in your spreadsheet. In our sample sheet, I’m placing the list fairly close to the data entry columns. Ideally, you’d put this list in some remote corner of the sheet where your data entry clerk can’t find it.
After you type the list of names, select that range of four cells and assign a name. (To do so, open the Insert menu, choose Name, Define, and then type a name such as Agentlist in the Define Name dialog box.)
Step 2: Select the data entry range and apply the data validation rule
At this point, you’re ready to tell Excel which cells to validate for acceptable Agent names. In our sample sheet, we’ll select cells A2:A25.
Once you’ve selected those cells, open the Data menu and select the Validation option. Click the Settings tab and then select List from the “Allow” drop-down list. Then, in the Source field, enter =Agentlist. (Alternatively, you could click the navigation tool and go select the range that contains your list. However, I find it easier to refer to named ranges.) Finally, since part of your validation rule includes “no blanks allowed,” deselect the checkbox labeled “Ignore blank,” as shown in Figure A.
|Here’s how you tell Excel to restrict data entry in a range of cells to the items in a particular list.|
Step 3: Add a nice touch to the user interface
Before you close the Data Validation dialog box, let’s add the “special touches” that will make this basic spreadsheet look like a polished application. First, click the Input Message tab and enter a title and a short message. Whatever you enter here will appear when the user has selected any of the cells in the range you’re validating. For example, you might type Agent Name as the title and Type an agent’s name or select one from the list for the input message, as shown in Figure B.
|The descriptive language you add here will appear when the user selects a cell in the Agent column.|
Step 4: Customize the error message
Now click the Error Alert tab and take a look at your options. By default, the “Style” field is set to Stop. If you accept this setting, the end user will not be able to enter anything in the target cell except for an entry in the master list. If you don’t want to get a support call from a frantic user, type something meaningful in the Title and Error message fields, as we’ve done in Figure C.
|If you’re not going to allow any exceptions to your validation rule, select “Stop” as the style for the error alert and enter some meaningful text so the user knows what’s going on.|
Once you’ve tweaked all of the settings in the Data Validation dialog box, click OK to put your validation rule into effect. Figure D shows what a user will see the first time he or she selects cell A2 in our sample sheet. If the user clicks the drop-down arrow beside that cell, the list of names will appear, as shown in Figure E.
And what happens if the user tries to enter Jeff, a name that isn’t in the master list? Figure F shows the custom error message that appears.
|Here’s what your user will see when one of the cells in column A is selected.|
|The dropdown list makes it easy for a user to select a name from the list of agents.|
|If an invalid name is entered, this error message appears and forces the user to try, try again.|
If you want to allow exceptions
In some rare instances, you’ll want to allow your user to choose a name from the dropdown list or enter a name that isn’t on the list. If so, you follow the same steps we described to set up the validation rule for a range, except you choose a different kind of error alert. In addition to Stop, your choices are “Warning” and “Information.” Those alerts have slightly different icons, but they do the same thing—they display a warning that forces the user to acknowledge the entry that isn’t on the “approved” list. However, unlike Stop, those error alerts don’t “kick out” the suspicious entry.
If you use the Warning error alert, you might want to use the error message to give your data entry clerk specific instructions. Figure G shows a sample warning message.
|If you choose to allow exceptions to your validation rule, use the Warning error alert and be sure to include special instructions, if required, for the data entry clerk.|
The list is only the beginning
For those of you who haven’t used Data Validation before, I thought setting up a simple “list” would be a good place to start. Of course, you can do much more than simply determine if an item exists in a master list.
You can set up rules to validate entries based on a range of whole numbers, decimals, or dates. You want to restrict a range of cells to values between 1 and 10 or 100 and 200—no problem! When you open the Data Validation dialog box, just choose Whole Number from the “Allow” drop-down list. When you do, you’ll get to select an operator (such as between or not between) and a minimum and maximum range.
If you’d like to share your favorite Data Validation tip, please post a comment below or send us a note.