Spot where two columns disagree — old vs new, expected vs actual. A simple <> rule highlights every row where the values don’t match.
The example
Rows where the two values differ are flagged.
| A | B | |
|---|---|---|
| 1 | Old | New |
| 2 | 100 | 100 |
| 3 | 250 | 275 |
The formula
The rule that flags mismatches:
How it works
A not-equal test per row:
- Select both columns (e.g. A1:B100), then add a formula rule
=$A1 <> $B1. - The column letters are locked (
$A,$B) but the row floats, so each row compares its own pair. - Cells in mismatching rows get the format; matching rows stay plain.
- For case-sensitive comparison, use
=NOT(EXACT($A1, $B1)).
Find matches instead with =$A1=$B1, or count the differences with =SUMPRODUCT(--(A1:A100<>B1:B100)). To compare whole rows across many columns, AND the column tests together.
Try it: interactive demo
Pairs “old,new”; differences highlight.
Variations
Case-sensitive
Treat case as a difference:
Highlight matches
Flip the test:
Count differences
How many disagree:
Pitfalls & errors
Lock columns, free rows. Use $A1/$B1 — relative rows so each line compares itself, absolute columns so it always looks at A and B.
Text vs number. “100” as text won’t equal 100 as a number — clean types first if a “difference” looks wrong.
Case is ignored by default. <> sees “abc” and “ABC” as equal; use EXACT for case sensitivity.
Practice workbook
Frequently asked questions
How do I highlight differences between two columns in Excel?
How do I make the comparison case-sensitive?
How do I count how many rows differ?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class