Data Validation

Because formulas are only as accurate as the data they receive, it's important that your spreadsheet contains only valid data. Examples of invalid data might be a negative number (such as -9) for a price or a decimal number (such as 4.39) for the number of items a customer bought.

To keep your spreadsheet from accepting invalid data, you can define a cell to accept only certain types of data, such as numbers that fall between 30 and 100. The moment someone tries to type invalid data into a cell, Excel immediately warns you, as shown in Figure below.

Excel warns you if you type invalid data in a cell.

To define valid types of data for a cell, follow these steps:

1. Click a cell that contains data used by a formula.
2. Click the Data tab.
3. Click the Data Validation icon in the Data Tools group.
   The Data Validation dialog box appears, as shown in Figure below.
   
   
   
   Define the type and range of acceptable data allowed in a cell.
   
4. Click the Allow list box and choose one of the following:
Any Value: The default value accepts anything the user types
Whole Number: Accepts only whole numbers, such as 47 and 903
Decimal: Accepts whole and decimal numbers, such as 48.01 or 1.00
List: Allows you to define a list of valid data
Date: Accepts only dates
Time: Accepts only times
Text length: Defines a minimum and maximum length for text
Custom: Allows you to define a formula to specify valid data

Depending on the option you choose, you may need to define Minimum
and Maximum values and whether you want the data to be equal to, less
than, or greater than a defined limit.

5. Click the Input Message tab in the Data Validation dialog box, as shown in Figure shown below.
6. Click in the Title text box and type a title.
   
   
   
   The Input Message tab lets you display a message explaining the type of valid data a
   cell can hold.
   
7. Click in the Input Message text box and type a message you want to display when someone selects this particular cell.
8. Click the Error Alert tab in the Data Validation dialog box, as shown in Figure below.
9. Click the Style list box and choose an alert icon, such as Stop or Warning.
10. Click in the Title text box and type a title for your error message.
    
    
    
    Define an error message to show if the user types invalid data into the cell.
    
11. Click in the Error Message text box and type the message to appear if the user types invalid data into the cell.
12. Click OK.

After you define data validation for a cell, you can always remove it later. To remove validation for a cell, follow these steps:

1. Click in the cell that contains data validation.
2. Click the Data tab.
3. Click the Data Validation icon in the Data Tools group. The Data Validation dialog box appears.
4. Click Clear All and then click OK.
5. Excel clears all your data validation rules for your chosen cell.

[Back] [Contents]