Formulas 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.

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
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.
6. Click in the Title text box and type a title.
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.
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.
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. Excel clears all your data validation rules for your chosen cell.