Excel 2019 Bible

Chapter 26: Using Data Validation

26

Generally, when entering a data validation formula for a range of cells, you use a reference

to the active cell, which is normally the upper-left cell in the selected range. An excep-

tion is when you need to refer to a specific cell. For example, suppose that you select range

A1:B10, and you want your data validation to allow only values that are greater than the

value in cell C1. You would use this formula:

=A1>$C$1

In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the

cells in the selected range, which is just what you want. The data validation formula for

cell A2 looks like this:

=A2>$C$1

The relative cell reference is adjusted, but the absolute cell reference is not.

Data Validation Formula Examples

The following sections contain a few data validation examples that use a formula entered

directly into the Formula control on the Settings tab of the Data Validation dialog box.

These examples help you understand how to create your own data validation formulas.

All the examples in this section are available at this book’s website at http://www.wiley.com/go/excel2019bible.
The file is named data validation examples.xlsx.

Accepting text only

Excel has a data validation option to limit the length of text entered into a cell, but it

doesn’t have an option to force text (rather than a number) into a cell. To force a cell or

range to accept only text (no values), use the following data validation formula:

=ISTEXT(A1)

This formula assumes that the active cell in the selected range is cell A1.

Accepting a larger value than the previous cell

The following data validation formula enables the user to enter a value only if it’s greater

than the value in the cell directly above it:

=A2>A1

This formula assumes that A2 is the active cell in the selected range. Note that you can’t

use this formula for a cell in row 1.