• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Data Validation

Excel allows you to set a validation criterion for any field/column to ensure that only valid data is entered. For example, a Zip Code field could be restricted to entries of exactly five digits. Validation tests are automatically applied as you add items to the list. You can set validation options when creating a list or afterwards (described in the following steps).

To set validation options

Click a cell in the column for which you'd like to set validation criteria.

Choose Data > Validation.

The Data Validation dialog box appears (Figure 12.16).

Figure 12.16. Specify a validation criterion on the Settings tab of the dialog box.

On the Settings tab, set a validation criterion by making choices from the drop-down lists and typing in the text box(es).

Optional: To prevent blank entries in the column, remove the checkmark from Ignore blank.

Optional: If you want a pop-up message to appear whenever you enter a cell in the column, click the Input Message tab. Enter a title and message text (Figure 12.17).

Figure 12.17. Enter an optional pop-up message to direct users who tab or click into a cell in this column.

Optional: To enter an error message that will appear whenever validation fails, click the Error Alert tab. Enter a title and message text, and choose a message style from the Style drop-down menu:

  • Stop. Prevent further work until the error is corrected.

  • Warning. Offer a choice of correcting or accepting the entered data.

  • Information. Display only explanatory text.

Click OK when you're done. If an incorrect value is later entered into a cell in this column, the error message (if there is one) is displayed (Figure 12.18).

Figure 12.18. When a Stop error message appears, the user can click Retry to correct the error or Cancel to delete the errant cell entry.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint