Using Data Validation in Excel 2007

Data Validation allows you to control the type and range of data that is entered into cells.  For example, you could designate a cell to allow only numerical entries between 15 and 500.  This way, you control the accuracy of the data that is entered into your worksheet.  Some of the options for Data Validation are:

• Whole numbers only between a specific range
• Decimal numbers between a specific range
• Allow list values from a particular cell range
• Date values only between a particular date range
• Time values only between a particular time frame
• Text values only between a specified text length
• Custom Values

After you designate what constitutes acceptable criteria, you can customize the error message the user receives when data that does not meet the specified criteria is entered.  You can also specify an informational input message that appears when the cell containing a validation rule is activated.

Note however, that Data Validation has some flaws.  For instance, users can bypass your validation rules by pasting data using the copy and paste features.  Also, existing data is not checked when you create a validation rule.  To ensure that your data meets your specified criteria, you can choose Circle Invalid Data from the Data Validation Button arrow.  Any values that are outside your data validation rules are circled in red.

To Use Data Validation:

1. Select the cell or cell range to which you wish to apply validation.
2. Click the Data tab on the Ribbon.
3. Click the Data Validation button on the Data Tools group.
4. On the Settings Tab, select the data type you wish to allow from the Allow drop-down list.
5. Enter any ranges and other settings as needed.
6. To disable the validation rules for blank cells, select Ignore Blank.
7. To activate an Input message, click the Input Message tab and then enter the Title and the Input Message text to be displayed to the user.
8. To display an error message when the validation rule is violated, click the Error Alert tab and then enter the Title and the Error Message to be displayed.  Ensure that the Show error alert after invalid data is entered check box is checked.
9. To clear all data validation rules, click the Data Validation button and on the Settings tab, click the Clear All button.
10. Click OK.
11. To flag any data that is outside of your validation rules, click the Data Validation button arrow and click Circle Invalid Data.

Similar Posts:

• Creating a drop-down list in Excel
• Creating an Input Mask in Access 2007
• Using Conditional Formatting based on Top/Bottom Values in Excel 2007
• Using Conditional Formatting in Excel 2007
• Creating Your Own Conditional Formatting Rules in Excel 2007
• Backup your Rules in Outlook 2007
• Applying Specialized Conditional Formatting in Excel 2007

Tagged as: Data Validation, Excel Tips, Microsoft Office Tips