Excel 2019 Bible

Chapter 26: Using Data Validation

26

Using Formulas for Data Validation Rules

For simple data validation, the data validation feature is quite straightforward and easy

to use. The real power of this feature, though, becomes apparent when you use the Custom

option and supply a formula.

The formula that you specify must be a logical formula that returns either TRUE or FALSE.

If the formula evaluates to TRUE, the data is considered valid and remains in the cell. If

the formula evaluates to FALSE, a message box appears that displays the message that you

specify on the Error Alert tab of the Data Validation dialog box.

Specify a formula in the Data Validation dialog box by selecting the Custom option from the

Allow drop-down list of the Settings tab. Enter the formula directly into the Formula con-

trol, or enter a reference to a cell that contains a formula. The Formula control appears on

the Settings tab of the Data Validation dialog box when the Custom option is selected.

We present several examples of formulas used for data validation in the section “Data

Validation Formula Examples” later in this chapter.

Understanding Cell References

If the formula that you enter into the Data Validation dialog box contains a cell reference,

that reference is considered a relative reference, based on the upper-left cell in the selected

range.

The following example clarifies this concept. Suppose you want to allow only an odd num-

ber to be entered into the range B2:B10. None of the Excel data validation rules can limit

entry to odd numbers, so a formula is required.

Follow these steps:

1. Select the range (B2:B10 in this example) and make sure that cell B2 is the
   active cell.


2. Choose Data ➪ Data Tools ➪ Data Validation. The Data Validation dialog box
   appears.


3. Select the Settings tab, and select the Custom option (from the Allow drop-
   down list).


4. Enter the following formula in the Formula field, as shown in Figure 26.5:

=ISODD(B2)

This formula uses the ISODD function, which returns TRUE if its numeric argument

is an odd number. Notice that the formula refers to the active cell, which is cell B2.