Validating Input

Excel's Data Validation command lets you perform validation on entries to set up data validation rules. You can define data that is acceptable for any given cell or range of cells , and input messages or error alerts that display whenever a validated cell is selected. Error alerts can display whenever a validated cell is selected or when a user tries to enter an invalid value. Data Validation only warns you against inputting invalid datait doesn't stop you from doing so.

Setting Validation Rules

Follow the steps in the next exercise to set up the data validation rules. You are setting rules for the Car data in column B on the Detail sheet.

To Do: Set Validation Rules

1. Click cell A12 on the Detail sheet. Type New York and press Enter. In cell A13, type Hard Cover and press Enter. Click cell A13 and then click the Increase Indent button on the Formatting toolbar.

2. In cells B13:D13, type the numbers 49, 80 , and 125 .

3. Select cells B13:D13. This step tells Excel what data you want to validate in the worksheet.

4. Choose Data, Validation. The Data Validation dialog box opens, as shown in Figure 16.8. This exercise uses all three tabs: Settings, Input Message, and Error Alert.

   Figure 16.8. The Data Validation dialog box.

   

5. First, in the Settings tab, click the Allow drop-down arrow and choose Whole Number.

6. In the Data list, ensure that Between is selected.

7. In the Minimum box, type 50 .

8. In the Maximum box, type 100 .

9. Click the Input Message tab.

10. In the Input Message box, type Any whole number from 50 to 100 .

11. Click the Error Alert tab.

12. In the Error Message box, type Hard cover sales are less than 50 or greater than 100 .

13. Click OK to confirm your entries. Excel shows the following text box next to cell B13: Any whole number from 50 to 100 . The next step is to edit an entry to produce invalid data in a cell.

14. Click cell D13 on the Detail sheet.

15. Type 35 and press Enter. Good job! Your error alert worked. Excel displays the error message Hard cover sales are less than 50 or greater than 100 .

16. Click the Cancel button to remove the message.

Circling Invalid Data

If your worksheet contains any invalid data, you can identify that data by using the Circle Invalid Data feature. You need to display the Formula Auditing toolbar to use the feature. Excel identifies invalid data by marking the data with a red circle. You can correct the invalid data by typing over the data in the cells that contain a red circle.

The instructions in the following To Do exercise step you through circling and correcting invalid data on your worksheet. First you need to enter invalid data for a car expense in the Detail sheet.

To Do: Circle and Correct Invalid Data

1. Choose Tools, Formula Auditing, Show Formula Auditing Toolbar. Excel displays the Formula Auditing toolbar.

2. Click the Circle Invalid Data button on the Formula Auditing toolbar. You should see a red circle around the 49 in cell B13 and the 125 in cell D13 (see Figure 16.9). The number 49 in cell B13 is less than 50 . The number 125 in cell D13 is greater than 100 . Therefore, the data is invalid.

   Figure 16.9. Circle around invalid data.

   

3. Click cell B13 and type 100 . Then press Enter. Click cell D13 and type 95 . Then press Enter. The red circle is removed from both cells.

4. If a red circle still shows in cell B13 and D13, click the Clear Validation Circles button on the Formula Auditing toolbar.

5. Click the Close ( x ) button on the Formula Auditing toolbar to close the toolbar.