Data entry errors are the bane of any Excel user who wants accurate results. Even if your formulas are perfect, if the data they process contains errors, then the results generated by those formulas are worthless. (Programming types have a saying for this phenomenon: “garbage in, garbage out”—often shortened to GIGO.) If you create spreadsheets for other people to use or if you share your workbooks with other users, how do you reduce (or even eliminate) the risk of bad data wrecking an otherwise good model?
The best way to do this is to use Excel’s data validation feature. This involves creating one or more rules that dictate the type of data or range of values that a user can enter into a cell. If the user tries to enter something that breaks a rule, you can configure Excel to display an error message and reject the entry.
Follow these steps to configure a data validation rule that allows only certain values in a cell:
1. Select the cell to which you want to apply the data validation rule.
2. Choose Data, Data Validation. Excel displays the Data Validation dialog box, shown in Figure 1.
3. In the Settings tab, open the Allow list and then select List.
4. In the Source box, you have two choices:
- If the list of allowable values exists in a worksheet, specify either the range or a range name that contains the list. (Precede the range or range name with an equals sign.)
- Type the allowable values directly into the Source box, separated by commas, as shown in Figure 5.1.
Figure 1: Use the Data Validation dialog box to set up a data validation rule that allows only certain values in a cell or range.
5. If you want the user to be able to select from the allowable values using a drop-down list, leave the In-Cell drop down check box activated.
6. To prevent blank entries, click to deactivate the Ignore Blank check box.
7. Click the Input Message tab.
8. If you want a pop-up box to display when the user selects the restricted cell or any cell within the restricted range, leave the Show Input Message When Cell Is Selected check box activated. Use the Title and Input Message boxes to specify the message that displays. For example, you can use the message to tell the user that a value for the cell must be chosen from the drop-down list.
9. Click OK to apply the data validation rule.
In Figure 2, I’ve applied the data validation rule shown in Figure 1 to the Ship Via cell, and you can see the drop-down list that displays.
Figure 2: When you set up a listbased validation rule, Excel displays a list of the possible choices for the cell.
Popularity: 1% [?]
If you liked this post, would you please buy me a twelve-ounce lattes for only $2
Recent Comments