Part of creating efficient and easy-to-use worksheets is to do what you can to ensure that the data entered into your worksheets is as accurate as possible. Although it isn't possible to catch every typographical or transcription error, you can set up a validation rule to make sure that the data entered into a cell meets certain standards.
To create a validation rule, open the Data Validation dialog box. You can use the Data Validation dialog box to define the type of data that Excel 2007 should allow in the cell and then, depending on the data type you choose, to set the conditions data must meet to be accepted in the cell. In the following graphic, Excel 2007 knows to look for a whole number value between 1000 and 2000.
Setting accurate validation rules can help you and your colleagues avoid entering a customer's name in the cell designated to hold their phone number or setting a credit limit above a certain level. To require a user to enter a numeric value in a cell, display the Settings page of the Data Validation dialog box, click the Allow down arrow, and, depending on your needs, choose either Whole Number or Decimal from the list that appears.
If you want to set the same validation rule for a group of cells, you can do so by selecting the cells to which you want to apply the rule (such as a column in which you enter the credit limit of customers of Consolidated Messenger) and setting the rule using the Data Validation dialog box. One important fact you should keep in mind is that Excel 2007 enables you to create validation rules for cells in which you have already entered data. Excel 2007 doesn't tell you whether any cells have data that violate your rule, but you can find out by having Excel 2007 circle any worksheet cells containing data that violates the cell's validation rule. To do so, display the Data tab of the user interface and then, in the Data Tools group, click the Data Validation button down arrow. On the menu that appears, click the Circle Invalid Data button to circle cells with invalid data.
When you're ready to hide the circles, click the Data Validation button down arrow and click Clear Validation Circles.
Of course, it's frustrating if you want to enter data into a cell and, when a message box appears that tells you the data you tried to enter isn't acceptable, you aren't given the rules you need to follow. Excel 2007 enables you to create messages that tell the user which values are expected before the data is entered and then, if the conditions aren't met, reiterate the conditions in a custom error message.
You can turn off data validation in a cell by displaying the Settings page of the Data Validation dialog box and clicking the Clear All button in the lower-left corner of the dialog box.
In this exercise, you create a data validation rule limiting the credit line of Consolidated Messenger customers to $25,000, add an input message mentioning the limitation, and then create an error message if someone enters a value greater than $25,000. After you create your rule and messages, you test them.
USE the Credit workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Focusing folder.
OPEN the Credit workbook.
CLOSE the Credit workbook.
CLOSE Excel 2007.