Restricting and Validating Data Entry for a Cell or Range

When designing a worksheet, you'll occasionally want to restrict the type of data users can enter in a specific cell or range. Excel lets you define data-validation rules for cells and ranges to do exactly that. Examples of useful applications include the following:

• In a list of recent sales results formatted to show only month and date, restrict entry in a specific column to only dates within the last month. This technique prevents users from inadvertently entering a date in the wrong month or year, or in the future.

• On a budget worksheet, require that the user enter a department name and restrict allowed entries to a specific list. You can add a drop-down arrow to a cell with this type of restriction so users can pick from a list.

• For purchase orders, check the amount a user enters against his or her authorized spending limit—say, $500. If the amount is over the limit, display a message that directs them to talk to a supervisor or re-enter the amount.

• Ask a user to enter a description in a form; to keep data to a manageable length, restrict the total number of characters the user can enter and display a warning message if the description exceeds that length.

• On an invoice form, allow a salesperson to enter an optional discount for good customers, but only if the amount before sales tax is over $100. Compare the entry in the Discount field with a formula that calculates the total purchases to validate the entry.

Defining Data-Validation Rules

Each data-validation rule has three components: the criteria that define a valid entry; an optional message you can display to users when they select the cell that contains the rule; and an error message that appears when users enter invalid data. To begin creating a data-validation rule, first select the cell or range for which you want to restrict data entry, and then choose Data, Validation. You'll see a Data Validation dialog box similar to the one in Figure 21.9.

On the Settings tab, enter the criteria that define a valid entry. First, choose the required data type in the Allow drop-down list; then define specific criteria. The available options in the Allow drop-down list (described in Table 21.1) vary depending on the type of data you select. Keep in mind that the options shown in the Data Validation dialog box change depending on the criteria you've selected in the Allow drop-down list. The dialog box shown in Figure 21.9 represents just one example.

Table 21.1. Data-Validation Settings

Data Type	Allowed Restrictions
Any Value	Default setting; no restrictions allowed. Select this option if you want to display a helpful input message only, without restricting data entry.
Whole Number, Decimal	Choose an operator (between, for example, or greater than) and values or formulas. The Whole Number data type produces an error if the user enters a decimal point, even if it's followed by zero. The Decimal choice allows any number after the decimal point.
List	In the Source box, enter the address or name of the range that contains the list of values you want to allow. The range can be on another worksheet (a hidden worksheet in the current workbook is your best choice) or in another workbook. For a short list, enter the valid items directly in this box, separated by commas (Acctg, Sales, Mktg). If you want users to be able to pick from a list, check the In-cell box.
Date or Time	Choose an operator and appropriate values. You can enter formulas here as well; for example, to allow only dates that have already occurred, choose Less Than from the Data box and enter =TODAY() in the End Date box.
Text Length	Choose an operator and then specify numbers that define the allowed length; you can also enter formulas or cell references that produce numbers as values for use with the selected operator.
Custom	Enter a formula that returns a logical value (TRUE or FALSE). Use this option when the cell that contains the rule is part of a calculation, and you want to test the results of that calculation rather than the cell value itself. On a purchase order with multiple items that you total in a cell named Total_PO, for example, enter =Total PO < 500 as the rule for each cell used in the SUM formula; that prevents the user from exceeding a $500 total limit even though each individual item is under the allowance.

Displaying Helpful Input Messages

Rules that stop users from entering invalid data are good, but helpful error messages are even better. As part of a data-validation rule, you can display messages that appear every time the user enters the cell that contains that rule. These messages appear in small pop-up windows alongside the cell. Use input messages to help users understand exactly what type of data they should enter in the cell, especially if you are designing a data-entry sheet for less-experienced Excel users.

To create an input message, choose Data, Validation. Click the Input Message tab (see Figure 21.10) and enter the title text and message you want to appear. Your message should be as helpful and brief as possible; if you've restricted the user to a particular type of data, make sure they know exactly what they're allowed to enter.

Alerting the User to Errors

How do you want Excel to respond when users enter invalid data? In all cases, you can display an error alert. If the data type is wrong, or if the date or value is not appropriate, you can refuse to accept the input and force users to enter an acceptable value. You can also choose to accept the value; this can be an effective way to force users to double-check values that might be valid but are outside of a normal range. On an expense report, for example, you might define valid entries as being below $2,000. If the amount users enter is over that amount, you could display a message that asks them whether they're sure the amount is correct. If they accidentally added an extra zero, the message will give them a chance to correct their mistake, or Excel can accept the input if they click OK.

To define an error message and set options for handling data that is outside the defined range, choose Data, Validation and click the Error Alert tab (see Figure 21.11).

Check the Show Error Alert After Invalid Data Is Entered box. Enter a title and text for the message you want users to see when they enter an invalid value. As with the Input Message, try to be as informative as possible so that the user knows exactly what he must do to correct the error. Then select one of the following choices from the Style box to define how Excel should handle the input:

• Stop— Displays a Stop dialog box and lets the user choose Retry or Cancel.

• Warning— Displays the error message and adds Continue? The user can choose Yes to enter the invalid data, No to try again, or Cancel.

• Information— Displays the error message. The user can click OK to enter invalid data or Cancel to back out.

Deleting, Moving, or Copying Data-Validation Rules

To remove all validation rules from a cell or range, first select the cell(s) containing the validation rule; then choose Data, Validation, and click the Settings tab. Click the Clear All button and click OK. This option erases the input message, error alert, and validation settings.

When should you select the Apply These Changes to All Other Cells with the Same Settings check box? If you originally create a set of validation rules for a range of cells, Excel stores those settings with the range. If you later adjust the settings for an individual cell in that range, you break the link to the range. Check this box while editing data-validation settings for a single cell, and Excel extends the selection and applies your changes to the entire range you originally selected. The check box has no effect on other cells for which you defined rules individually, even if the rules are absolutely identical.

Are you still finding invalid data in a user form in which you've created validation rules to protect data? See "Data Validation Limitations" in the "Troubleshooting" section at the end of this chapter.

Troubleshooting Data Errors

Data-validation rules are not perfect. Users can bypass the rules and enter invalid data by pasting from the Clipboard, or by entering a formula that results in an invalid value. Also, Excel does not check the existing contents of a cell or range when you create or copy a validation rule. When you audit a worksheet, Excel finds cells that contain values that are outside the limits you defined with data-validation rules. This technique is the only way to find incorrect values on a worksheet.

These auditing tools are not available from any menu. The only way to identify invalid data is to click a button on the Formula Auditing toolbar; curiously, this option never appears on the list of toolbars that Excel displays when you right-click an existing toolbar. To display the Auditing toolbar, choose Tools, Formula Auditing, Show Formula Auditing Toolbar. Click the Circle Invalid Data button to show any cells that are outside the rules, as shown in Figure 21.12; click the Clear Validation Circles button to clear the highlights.

→ For an overview of other tools you can use to track down problems in formulas, see "Troubleshooting Formulas".