Overview and samples of data validation

What is data validation?

Microsoft Excel data validation lets you define what type of data you want entered in a cell. For example, you can allow entry of a letter grade with only the letters A through F. You can set up data validation to prevent users from entering data that isn't valid, or allow invalid data but check for it after the user is finished. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.

When data is entered that doesn't meet your requirements, Excel displays a message with instructions you provide.

Data validation is particularly useful when you're designing forms or worksheets that other people will use to enter data, such as budget forms or expense reports.

This article describes how to set up data validation, including the types of data you can validate and the messages you can display, and provides a workbook that you can download to get samples of validation that you can modify and use on your own worksheets.

Types of data you can validate

Excel lets you designate the following types of valid data for a cell:

Numbers     Specify that the entry in a cell must be a whole number or a decimal number. You can set a minimum or maximum, exclude a certain number or range, or use a formula to calculate whether a number is valid.

Dates and times     Set a minimum or maximum, exclude certain dates or times, or use a formula to calculate whether a date or time is valid.

Length     Limit how many characters can be typed in a cell, or require a minimum number of characters.

List of values     Make a list of the choices for a cell — such as small, medium, large — and allow only those values in the cell. You can display a dropdown arrow when a user clicks the cell to make it easy to pick from your list.

Types of messages you can display

For each cell you validate, you can display two different messages: one that appears before the user enters data, and one that appears after the user tries to enter data that doesn't meet your requirements. If users have the Office Assistant turned on, the Assistant displays the messages.

Input message     This type of message appears as soon as a user clicks the validated cell. You can use it to provide instructions about the type of data you want entered in the cell.

Error message     This type of message appears only when the user types data that isn't valid and presses ENTER. You can choose from three types of error messages:

• Information message     This message does not prevent entry of invalid data. In addition to the text you provide, it has an information icon, an OK button, which enters the invalid data in the cell, and a Cancel button, which restores the previous value to the cell.
• Warning message     This message does not prevent entry of invalid data. It has the text you provide, a warning icon, and three buttons: Yes enters the invalid data in the cell, No returns to the cell for further editing, and Cancel restores the previous value to the cell.
• Stop message     This message won't allow invalid data to be entered. It has text you provide, a stop icon, and two buttons: Retry returns to the cell for further editing, and Cancel restores the previous value to the cell. Note that this message isn't intended as a security measure: although users can't enter invalid data by typing and pressing ENTER, they can circumvent the validation by copying and pasting or filling data in the cell.

If you don't specify any messages, Excel flags whether the data a user enters is valid so that you can check for it later, but does not notify the user when an entry is invalid.

Examples of validation

The best way to understand what data validation can do is to see it in action. If you download the sample workbook that accompanies this article, you'll get examples of each of the types of validation and all the message types. The workbook includes instructions for viewing the settings used for each validated cell, and copying the validation settings to your own workbooks, where you can modify them to suit your uses.

Setting up data validation

Once you know what validation you want to use on a worksheet, you can use the Validation command on the Data menu to set it up. You'll find instructions in the sample workbook that you can download from this article as well as in Excel 2002 Help. Here's a general overview of the process:

1. Set up your worksheet     Start by entering the data and formulas on your worksheet. If you're using a list of valid choices, enter and name your list.
2. Define the settings for a cell     Beginning with the first cell you want to validate, use the Data Validation dialog box to designate the type of validation you want, an input message if you want one, and an error message if you want one.
3. Set up validation for other cells     You can often save time by copying the validation settings from the first cell to other cells and then modifying the settings.
4. Test your validation rules     Try entering both valid and invalid data in the cells to make sure your settings are working as you intended and your messages are appearing when you expect. Use the Validation command to make any changes to the settings. If you make changes to the validation in one cell, you can automatically apply your changes to all other cells that have the same settings.
5. Set up your lists of valid choices     If you used a list of valid choices and don't want users to be able to find and change the list, you can put the list on another worksheet, set up the validation, hide the worksheet that contains the list, and then help protect the workbook with a password. The workbook password will help guard the worksheet that contains the list from others.
6. Apply protection, if desired     If you're planning to protect the worksheet or workbook, do that after you're finished setting up validation. Make sure you unlock any validated cells before protecting the worksheet, otherwise users won't be able to type in the cells.
7. Share the workbook, if desired     If you're planning to share the workbook, do that after you're finished setting up validation and protection. After you share a workbook, you won't be able to change the validation settings unless you stop sharing, but Excel will continue to validate the cells you've designated while the workbook is shared.
8. Check the results for invalid data     After users enter data in the worksheet, you can check for invalid data as described later in this article.

Entering data in validated cells

Here's what the process of entering data is like for users. You can use input and error messages to provide the instructions users need to understand how you've set up the worksheet to ensure correct data. To see what this is like, download the sample workbook that accompanies this article and look at the examples on the Messages sheet.

Viewing your input message     When a user clicks a validated cell or uses the arrow keys to move into the cell, your input message appears either in an Assistant balloon or a separate message box. If you provided a dropdown list for the cell, the dropdown arrow appears to the right of the cell.

Typing data     As the user types data or clicks the dropdown arrow to select a value from your list, the input message stays on the screen (the dropdown list may cover part of your message).

Entering valid data     If the user types valid data and presses ENTER, the data is entered in the cell and nothing special happens.

Entering invalid data     If the user types data that doesn't meet your criteria, and you specified an error message for invalid data, your message appears either in an Assistant balloon or a separate message window. The user can then read the message and decide what to do.

• For an information message, the user can click OK to enter the invalid data, or click Cancel to start over.
• For a warning message, the user can click Yes to enter the invalid data, No to edit the cell some more, or Cancel to start over.
• For a stop message, the user can't enter the invalid data, and can either click Retry to edit the cell or Cancel to start over.

If you don't provide messages, entering data in validated cells is the same for users as regular Excel data entry. However, Excel does flag any cells that have invalid entries so you can easily find those entries.

Checking a worksheet for invalid entries

When you receive worksheets back from users who may have entered invalid data, you can have Excel display red circles around any data that didn't meet your criteria, making it easy to scan the worksheet for errors. Use the Circle Invalid Data and Clear Validation Circles buttons on the Auditing toolbar for this purpose.

The value in this cell is circled because it does not meet a validation rule.

When you correct the data within the cell, the circle disappears. To see what this feature is like, download the sample workbook that accompanies this article, and look at the Invalid Data sheet.