Excel Annoyances

DATA VALIDATION ANNOYANCES

RESTRICT DATA ENTRY WITH VALIDATION RULES

The Annoyance:

I’m a manager at an engineering company, and I hired an out-of-work political science major to do data entry and other clerical stuff. We don’t pay him a lot, but our deal is that he gets to learn about computers on our dime when he’s doing data entry. There’s only one problem: he types so badly that he makes a ton of mistakes—adding extra numbers, leaving some out, even hitting letters instead of numbers. Isn’t there some way Excel can flag a mistake before he enters the data?

The Fix:

The secret is using data validation. Click a cell (or a group of selected cells), and then turn on the validation feature by selecting Data → Validation and clicking the Settings tab. From the Allow drop-down menu, pick the type of validation criteria you want to use, and then specify its parameters from the drop-down menus that appear below. The type of validation you select in the Allow drop down determines what other options appear below. If you select Whole number, for example, you can specify whether the entered values should be between two other values, not between two other values, or less than or greater than a value. You can hardcode the value by picking “equal to” from the Data drop-down menu and typing the value into the Value field. Figure 1-19shows the types of criteria you can create.

Figure 1-19. Limiting ...