Chapter 26: Using Data Validation
26
- Select the Settings tab.
- Choose an option from the Allow drop-down list. The contents of the Data
Validation dialog box change, displaying controls based on your choice. For exam-
ple, to specify a formula, select Custom. - Specify the conditions by using the displayed controls. Your selection in step 4
determines the other controls that you can access. - (Optional) Select the Input Message tab, and specify which message to display
when a user selects the cell. You can use this optional step to tell the user what
type of data is expected. If this step is omitted, no message will appear when the
user selects the cell. - (Optional) Select the Error Alert tab and specify which error message to dis-
play when a user makes an invalid entry. The selection for Style determines what
choices users have when they make invalid entries. For example, choose Stop to
prevent an invalid entry. If this step is omitted, a standard message will appear if
the user makes an invalid entry.
Even with data validation in effect, a user can enter invalid data. If the Style setting on the Error Alert tab of the Data
Validation dialog box is set to Warning or Information, invalid data can be entered. You can identify invalid entries by
having Excel circle them (explained in the next section).- Click OK. The cell or range contains the validation criteria you specified.
Types of Validation Criteria You Can Apply
From the Settings tab of the Data Validation dialog box, you can specify a variety of data
validation criteria. The following options are available from the Allow drop-down list. Keep
in mind that the other controls on the Settings tab vary, depending on your choice from
the Allow drop-down list.Any Value Selecting this option removes any existing data validation. Note, however,
that the input message, if any, still displays if the box is checked on the Input Message tab.
Whole Number The user must enter a whole number. You specify a valid range of whole
numbers by using the Data drop-down list. For example, you can specify that the entry
must be a whole number greater than or equal to 100.
Decimal The user must enter a number. You specify a valid range of numbers by refining
the criteria from choices in the Data drop-down list. For example, you can specify that the
entry must be between 0 and 1.