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. While it isn t possible to catch every typographical or transcription error, you can set up a validation rule to make sure the data entered into a cell meets certain standards.
To create a validation rule, you open the Data Validation dialog box.
You can use the Data Validation dialog box to define the type of data that Excel 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 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 where you enter the credit limit of customers of The Garden Company) and setting the rule using the Data Validation dialog box. One important fact you should keep in mind is that Excel lets you create validation rules for cells where you have already entered data. Excel doesn t tell you if any cells have data that violate your rule, but you can find out by having Excel circle any worksheet cells containing data that violates the cell s validation rule. To do so, you display the Tools menu, point to Formula Auditing, and click Show Formula Auditing Toolbar. On the Formula Auditing toolbar, click the Circle Invalid Data button to circle cells with invalid data.
When you re ready to hide the circles, display the Formula Auditing toolbar and click the Clear Validation Circles button.
Of course, it s frustrating if you want to enter data into a cell and, when a message box appears, telling you the data you tried to enter isn t acceptable, you aren t given the rules you need to follow. Excel lets you create messages that tell the user what 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 The Garden Company customers to $2,500, add an input message mentioning the limitation, and then create an error message should someone enter a value greater than $2,500. After you ve created your rule and messages, you test them.
USE the Validate.xls document in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Office 2003 SBS\UsingFilters folder, and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .
OPEN the Validate.xls document.
Select cells K4 through K7.
On the Data menu, click Validation .
The Data Validation dialog box appears with the Settings tab page in front.
In the Allow box, click the down arrow and, from the list that appears,click Whole Number .
Boxes labeled Minimum and Maximum appear below the Data box.
In the Data box, click the down arrow and, from the list that appears, click less than or equal to .
The Minimum box disappears.
In the Maximum box, type 2500 .
Clear the Ignore blank check box.
Click the Input Message tab.
The Input Message tab page appears.
In the Title box, type Enter Limit .
In the Input Message box, type Please enter the customer s credit limit, omitting the dollar sign .
Click the Error Alert tab page.
The Error Alert tab page appears.
In the Style box, click the down arrow and, from the list that appears, choose Stop .
The icon that will appear in your message box changes to the Stop icon.
In the Title box, type Error , and then click OK .
Click cell K7.
A ScreenTip with the title Enter Limit and the text Please enter the customer s credit limit, omitting the dollar sign appears near cell K7.
Type 2501 , and press [ENTER].
A stop box with the title Error and default text appears.
Leaving the Error message box blank causes Excel to use its default message: The value you entered is not valid. A user has restricted values that can be entered into this cell.
Click Cancel .
The error box disappears.
Clicking Retry lets you edit the bad value, while clicking Cancel deletes the entry.
Click cell K7.
Cell K7 becomes the active cell, and the ScreenTip reappears.
Type 2500 , and press [ENTER].
Excel accepts your input.
On the Tools menu, point to Formula Auditing , and click Show Formula Auditing Toolbar .
The Formula Auditing toolbar appears.
On the Formula Auditing toolbar, click the Circle Invalid Data button.
A red circle appears around the value in cell K4.
On the Formula Auditing toolbar, click the Clear Validation Circles button.
The red circle around the value in cell K4 disappears.
On the Formula Auditing toolbar, click the Close box.
The Formula Auditing toolbar disappears.
On the Standard toolbar, click the Save button.
CLOSE the Validate.xls document.