A common spreadsheet pattern is “compare two numbers, then highlight the one that needs attention.” You might be tracking inventory, goals vs actuals, hours worked vs hours planned, or anything where one value should not drift past another.

Conditional formatting lets you do this automatically: the cell changes color when the relationship between two cells matches your rule.

Below are the most useful ways to compare two cells and highlight one based on quantities, including the gotchas that usually cause “why is this highlighting?” moments.

---

The Idea: Conditional Formatting Uses a Formula That Must Return TRUE

When you apply conditional formatting with a custom formula, the spreadsheet evaluates that formula for each row (or each cell in the range). If the result is TRUE, the format applies. If it’s FALSE, nothing happens.

The key is writing the formula so it compares:

• a target cell (the one you want highlighted)
• to a reference cell (the one you’re measuring against)

Example mental model:

• Highlight I4 if it is greater than J4
• Highlight I4 if it is less than J4
• Highlight I4 if it is different than J4
• Highlight I4 if it’s blank when J is not blank, etc.

---

Absolute vs Relative References (The Most Important Detail)

When you format a range like I4:I, you typically want:

• the column letter locked (so it always compares I to J)
• the row number relative (so it changes per row automatically)

So you usually use:

• $I4 and $J4

The $ before the column means “always use this column.”
The row number (4) shifts to 5, 6, 7 as the rule applies down the column.

---

Basic Comparisons

1) Highlight I if I is greater than J

=$I4>$J4

2) Highlight I if I is less than J

=$I4<$J4

3) Highlight I if I equals J

=$I4=$J4

4) Highlight I if I does not equal J

=$I4<>$J4

---

“Only Highlight When Both Cells Have Numbers” (Avoid Blank Weirdness)

Blanks can behave like zeros in certain comparisons, and that can cause unexpected formatting. A reliable pattern is to require both cells to be filled.

Highlight I if I > J, but only when both are not blank

=AND($I4<>"",$J4<>"",$I4>$J4)

This prevents:

• highlighting when I is empty
• highlighting when J is empty

---

“Ignore Zeros” (Treat 0 as “Not a Real Value”)

Sometimes 0 is a real value, sometimes it’s a placeholder. If you want the rule to ignore zeros in the comparison cell:

Highlight I if I > J, but only when J is not blank and not 0

=AND($I4<>"",$J4<>"",$J4<>0,$I4>$J4)

This guarantees:

• if J = 0, it won’t highlight no matter what I is

---

Comparing With Multiple Conditions

Highlight I if I is behind J (I < J), but only when J exists

=AND($I4<>"",$J4<>"",$I4<$J4)

Highlight I if I is ahead of J (I > J), but only when J exists

=AND($I4<>"",$J4<>"",$I4>$J4)

---

“Two-Branch Logic” (Different Rules Depending on Whether a Cell Is Blank)

Sometimes you want:

• One behavior when J has a value
• Another behavior when J is blank

Example: highlight I if it exceeds J, but if J is blank, highlight I when it’s at least 1.

=OR(
  AND($J4<>"",$I4>$J4),
  AND($J4="",$I4>=1)
)

This is useful when blank means “no target set yet” but you still want to flag non-zero values.

If you also want to prevent blank I from ever highlighting, add $I4<>"" as a gatekeeper:

=AND(
  $I4<>"",
  OR(
    AND($J4<>"",$I4>$J4),
    AND($J4="",$I4>=1)
  )
)

---

Common Mistakes That Cause “It’s Highlighting the Wrong Stuff”

1) Referencing the wrong column

If you are formatting column I but your formula compares K and J, you’ll get confusing results. The “target cell” in the formula should match the formatted range.

2) Applying the rule to the wrong range

If you want to highlight I, your apply-to range should be something like:

• I4:I (Google Sheets)
• or I4:I1000

3) Forgetting to lock the column with $

If you use I4>J4 without $, then as conditional formatting evaluates across columns, it can shift references unexpectedly (especially if you apply it to multiple columns).

---

Quick Setup Steps (Google Sheets)

1. Select the range you want highlighted (example: I4:I)
2. Format → Conditional formatting
3. “Format cells if” → Custom formula is
4. Paste your formula (example: =AND($I4<>"",$J4<>"",$I4>$J4))
5. Choose a fill color

Excel is similar: Conditional Formatting → New Rule → Use a formula to determine which cells to format.

---

A Simple “Best Default” Formula

If your goal is: highlight I when it is greater than J, but only when both are real values:

=AND($I4<>"",$J4<>"",$I4>$J4)

If you also want to ignore J=0:

=AND($I4<>"",$J4<>"",$J4<>0,$I4>$J4)