Jump to content

How to Restrict Data Entry with Validation Rules in Excel

  CurtFrye's Photo
Posted Sep 29 2009 09:35 AM

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 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 shows the types of criteria you can create.

Figure 1. Limiting your colleague's actions was never so easy.

Attached Image

For instance, if he's supposed to be typing in Zip Codes, and you want to make sure he doesn't leave out digits or add extra ones, set the Text length validation rule to require a number between 5 and 5. If he's supposed to enter 94607 and he types 9460 by mistake, he'll receive an error message reading "The value you entered is not valid."

If you want to make sure he doesn't forget to enter data in a critical cell, uncheck the "Ignore blank" box in the Data Validation dialog. That forces him to enter something in the cell. (If you want, you can determine exactly what he needs to enter by selecting the appropriate entry in the Allow drop down.) If the "Ignore blank" box is grayed out, temporarily select anything except "Any value" in the Allow drop down; this makes the "Ignore blank" box accessible. After you uncheck it, you can return to "Any value" if you want, and the "Ignore blank" box, though grayed out, will remain unchecked so that he will have to enter something in the cell.

If you want to keep users from typing data into a cell, you can restrict them to selecting entries from a list. The trick: select List from the Allow drop-down menu. When you do, a field titled Source appears. You can define the list's values by either selecting a range of cells from the worksheet, or typing values into the Source field, with each one separated by a comma. Entries in the Source field can include spaces, semicolons, and almost any other character. The only character you can't use is the one that separates entries, i.e., the comma. For example, your list of categories might look like this: Service Plans, Accessories, Sales, On-Site.

Excel Annoyances

Learn more about this topic from Excel Annoyances.

Excel Annoyances addresses all of the quirks, bugs, and hidden features found in the various versions of the Excel spreadsheet program. Broken down into several easy-to-follow categories such as Entering Data, Formatting, Charting, and Printing, Excel Annoyances uncovers a goldmine of helpful nuggets that you can use to maximize Excel's seemingly limitless potential.

See what you'll learn

0 Subscribe

1 Reply

  janetkissho's Photo
Posted Dec 17 2013 06:56 AM

Hey, I really appreciate the tips I have learned, I saw much about data entry here too. Both of you did a great job in making us learn something better.