Cross-Cell Validation Rules

What Are Cross-Cell Rules?

Every validation rule you've written so far checked the value of a single cell against a fixed condition — a number range, a list, a text pattern. Cross-cell validation goes further: it checks the value being entered against another cell in the same row.

How Excel Handles Cross-Cell References

When you write a custom validation formula for a cell in row 2, you can freely reference any other cell in that same row. Excel adjusts the references automatically as the rule applies down the column — just like a regular formula.

Example 1: End Date Must Be After Start Date

This is the most common cross-cell rule. An End Date entered before the Start Date is a logical error that no built-in rule can catch — but a custom formula handles it cleanly.

So if your validation on column L (End Date) references column K (Start Date):

=L2>K2

Excel automatically evaluates L3>K3, L4>K4, and so on for every validated cell below. If the End Date is earlier than or equal to the Start Date, the entry is rejected.

Example 2: Discount Requires Minimum Quantity

A discount should only be applied when the order quantity justifies it. Assuming Quantity is in column H and Discount % in column J:

=IF(J2>0, H2>=3, TRUE)

This reads: if a discount is being entered, quantity must be at least 3 — otherwise always accept.

The TRUE at the end is important — it ensures that rows with zero discount pass without any quantity check.

Example 2: Unit Price Must Match Category

Tech products should never be priced below 500. Office products have no such restriction. Assuming Category is in column D and Unit Price in column I:

=IF(D2="Tech", I2>=500, TRUE)

This reads: if the category is Tech, enforce the minimum price — otherwise accept any price.

Building Cross-Cell Rules Safely

A few things to keep in mind:

• Always anchor the correct column — use relative row references (H2, not $H$2) so the formula adjusts down the column correctly;
• Account for blank cells — if a referenced cell might be empty, wrap your formula in an IF or use IFERROR to avoid unexpected rejections;
• Test with edge cases — equal dates, zero quantities, empty fields — these are where cross-cell rules most often break.

Task

1. Apply a cross-cell validation to the End Date column:

   • Formula: =L2>K2
   • Error style: Stop
   • Error message: "End Date must be after Start Date"

2. Apply a cross-cell validation to the Discount % column:

   • Formula: =IF(J2>0, H2>=3, TRUE)
   • Error style: Warning
   • Error message: "Discount should only be applied for quantities of 3 or more"

3. Apply a cross-cell validation to the Unit Price column:

   • Formula: =IF(D2="Tech", I2>=500, TRUE)
   • Error style: Stop
   • Error message: "Tech products must be priced at 500 or above"

4. Apply a cross-cell validation to the Start Date column:

   • Formula: =K2>=B2
   • Error style: Stop
   • Error message: "Start Date cannot be before Order Date"

5. Test each rule by entering deliberately conflicting values across columns — confirm all four rules fire correctly;

6. Test with blank cells in the referenced columns — note any unexpected behavior and consider how you would handle it.