Defining Valid Sets of Values for Ranges of Cells

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.

1.	Select the cell range J4:J7.
2.	On the Data tab of the user interface, in the Data Tools group, click Data Validation. The Data Validation dialog box appears with the Settings tab page in front.
3.	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.
4.	In the Data box, click the down arrow and, from the list that appears, click less than or equal to. The Minimum box disappears.
5.	In the Maximum box, type 25000.
6.	Clear the Ignore blank check box.
7.	Click the Input Message tab. The Input Message tab page appears.
8.	In the Title box, type Enter Limit.
9.	In the Input Message box, type Please enter the customer's credit limit, omitting the dollar sign.
10.	Click the Error Alert tab page. The Error Alert tab page appears.
11.	In the Style box, click the down arrow and, from the list that appears, choose Stop. The icon that appears on your message box changes to the Stop icon.
12.	In the Title box, type Error and then click OK.
13.	Click cell J7. A ScreenTip with the title Enter Limit and the text Please enter the customer's credit limit, omitting the dollar sign appears near cell J7.
14.	Type 25001 and press . A stop box with the title Error and default text appears. Tip Leaving the Error message box blank causes Excel 2007 to use its default message: The value you entered is not valid. A user has restricted values that can be entered into this cell.
15.	Click Cancel. The error box disappears. Important Clicking Retry enables you to edit the bad value, whereas clicking Cancel deletes the entry.
16.	Click cell J7. Cell J7 becomes the active cell, and the ScreenTip reappears.
17.	Type 25000, and press . Excel 2007 accepts your input.
18.	On the Data tab, in the Data Tools group, click the Data Validation button down arrow and then click Circle Invalid Data. A red circle appears around the value in cell J4.
19.	On the Data tab, in the Data Tools group, click the Data Validation button down arrow and then click Clear Validation Circles. The red circle around the value in cell K4 disappears.

CLOSE the Credit workbook.

CLOSE Excel 2007.