Data validation rules in Excel - Cell-by-Cell Formula Trace

Sample Data

This column lists ages entered by users. We want to allow only ages between 18 and 59.

Cell	Value
A1	Age
A2	25
A3	40
A4	15
A5	60
A6	10

Formula Trace

AND(A2>=18, A2<=59)

Step 1: A2>=18

Step 2: A2<=59

Step 3: AND(TRUE, TRUE)

Cell Reference Map

    A   
  +-----+
1 | Age |
  +-----+
2 | 25  | <-- referenced by formula
  +-----+
3 | 40  |
  +-----+
4 | 15  |
  +-----+
5 | 60  |
  +-----+
6 | 10  |
  +-----+

The formula checks the value in cell A2 to see if it is between 18 and 59 inclusive.

Result

    A       B
  +-------+---------+
1 | Age   | Valid?  |
  +-------+---------+
2 | 25    | TRUE    |
  +-------+---------+
3 | 40    | TRUE    |
  +-------+---------+
4 | 15    | FALSE   |
  +-------+---------+
5 | 60    | FALSE   |
  +-------+---------+
6 | 10    | FALSE   |
  +-------+---------+

The formula in column B shows TRUE for values between 18 and 59 inclusive, and FALSE otherwise.

Sheet Trace Quiz - 3 Questions

What does the formula AND(A2>=18, A2<=59) check?

A If the value in A2 is exactly 18 or 59

B If the value in A2 is between 18 and 59 inclusive

C If the value in A2 is less than 18 or greater than 59

D If the value in A2 is not a number

Key Result

AND(condition1, condition2) returns TRUE only if all conditions are TRUE, used here to validate a range.