Highlighting Differences in Excel: Conditional Formatting That Actually Works
A conditional formatting rule either highlights exactly the rows you want or every row in the sheet. The difference between the two is almost always one dollar sign.
Short answer
=$A2<>$B2 to flag rows where column A differs from column B, or =COUNTIF($D:$D,$A2)=0 to flag values missing from column D. Set the Applies To range to start on the same row as the formula.Before anything else: the reference rule
Every conditional formatting rule that works on a range of rows follows the same rule about references, and getting it wrong is why most rules highlight everything or nothing.
| Reference | What it does as the rule is applied down the range | When to use |
|---|---|---|
$A$2 | Always points at cell A2 — the same cell for every row | Almost never |
$A2 | Locked to column A, moves down with each row | Any rule comparing the current row |
A$2 | Locked to row 2, moves across columns | Headers, rarely |
A2 | Moves both across and down | Comparisons that are deliberately diagonal — almost never in practice |
The Applies To range must match the formula's first row
Rule 1: highlight rows where two columns differ
This is the base case — two columns of values that should agree, and you want to see where they do not.
| Setting | Value |
|---|---|
| Applies to | $A$2:$D$5000 (the whole rows you want highlighted) |
| Formula | =$A2<>$B2 |
| Meaning | Highlight the row when the key in column A is not equal to the value in column B |
Using <> alone treats any difference as a difference, including a rounding difference of one paisa. To apply a tolerance, compare the absolute difference instead:
=ABS($C2-$D2)>1
That rule highlights only rows where the amounts differ by more than one rupee — which is usually what you actually mean when you say two amounts “do not match”.
Rule 2: highlight an entire row based on one column
If you want the whole record visible rather than just the offending cell, apply the rule to the full row range and lock the column that carries the condition.
| Setting | Value |
|---|---|
| Applies to | $A$2:$F$10000 |
| Formula | =$F2="Amount difference" |
| Result | Every row whose status column says “Amount difference” is highlighted across all six columns |
The dollar before the F is what makes the rule refer to the status column for every row while still moving down. Remove it and the rule compares each cell in the row to the cell next to it, which produces a diagonal pattern of highlighting that means nothing.
Rule 3: highlight values missing from another list
This is the formatting version of the question people most often want answered — which of my invoices does not appear in the other list?
| Setting | Value |
|---|---|
| Applies to | $A$2:$A$10000 |
| Formula | =COUNTIF($D:$D,$A2)=0 |
| Meaning | Highlight any value in column A that appears zero times in column D — in other words, it is missing from the other list |
COUNTIF is not case-sensitive
COUNTIF treats INV/2025/0417 and inv/2025/0417 as the same value. For invoice numbers, employee codes and anything else where case is part of the identifier, wrap the comparison in EXACT — for example =SUMPRODUCT(--EXACT($D$2:$D$5000,$A2))=0. It is slower, but it is correct.Rule 4: highlight duplicates — and only the actual duplicates
Excel's built-in “Highlight Cells Rules → Duplicate Values” highlights every occurrence of a duplicated value, including the first one. That is useful for finding that a duplicate exists, and useless for deciding which row to delete.
| What you want to highlight | Formula |
|---|---|
| Every occurrence, including the first | =COUNTIF($A:$A,$A2)>1 |
| Only the second and later occurrences | =COUNTIF($A$2:$A2,$A2)>1 |
| Only the first occurrence | =AND(COUNTIF($A:$A,$A2)>1, COUNTIF($A$2:$A2,$A2)=1) |
| Values appearing exactly twice | =COUNTIF($A:$A,$A2)=2 |
| Duplicates within a combination of two columns | =COUNTIFS($A:$A,$A2,$B:$B,$B2)>1 |
The expanding-range trick in the second row is the one worth remembering. $A$2:$A2 starts anchored at row 2 and grows by one row as the rule is applied down the sheet, so the first occurrence of a value is never counted and every later one is. Applied to a duplicate list, it leaves the record you want to keep unhighlighted and marks the ones to remove.
Duplicates across two different files cannot be formatted
Rule 5: case-sensitive comparison
Where case is part of the identifier, none of the standard comparison operators are safe. EXACT is the only reliable test.
| Condition | Formula |
|---|---|
| Highlight where the two differ, including by case | =NOT(EXACT($A2,$B2)) |
| Highlight where the two match only if case matches | =EXACT($A2,$B2) |
| Highlight a value missing from another column, case-sensitive | =SUMPRODUCT(--EXACT($D$2:$D$5000,$A2))=0 |
For a reconciliation of invoice numbers this matters more than people expect. A supplier whose invoice numbering includes case — rare, but it happens — will produce a comparison where half the differences are not differences at all.
Rule 6: highlight changes between two sheets in one workbook
Because the data is in one file, a rule on Sheet1 can read Sheet2. This is the one comparison the formatting approach is genuinely good at.
| Setting | Value |
|---|---|
| Applies to (on Sheet1) | $A$2:$D$5000 |
| Formula | =ABS($D2-IFERROR(XLOOKUP($A2,Sheet2!$A:$A,Sheet2!$D:$D),$D2))>1 |
| Meaning | Highlight the row where the amount differs from the matching row on Sheet2 by more than a rupee |
Steps: applying a rule properly
Decide what you are highlighting before you open the dialog
Write the formula in a helper column first
Select the Applies To range with the right anchor row
Enter the rule with library references locked and rows relative
Test on a small range first
Keep the helper column even after the rule works
What formatting cannot do
- It does not change any value. A highlighted cell still contains exactly what it contained before. You cannot filter on a fill colour and get a reliable result, and you cannot count highlighted rows.
- It cannot reach another workbook. Rules are scoped to the file, so a comparison across two files has to be formula-based. This is a limitation of Excel, not a setting.
- It says nothing about why a cell differs. A red fill tells you there is a difference; it does not tell you whether the cause is a rate, a discount, a credit note or a duplicate.
- It cannot be exported meaningfully. Copy a highlighted range into another file and print it, and unless the rule is recreated for that range the colour does not follow. Values do.
- Whole-column references are expensive. A rule using
$A:$AinsideCOUNTIF, applied to tens of thousands of rows, evaluates the full column repeatedly. Limit the ranges to the actual data — it is the difference between a file that recalculates instantly and one that does not.
Formatting shows you the problem; it does not give you the list
Conditional formatting is excellent at the visual pass: scan a sheet, see what stands out. The difficulty is the moment after that, when somebody asks how many differences there are, or asks you to send the list. A fill colour cannot answer either question.
The practical rule is to use formatting as a second layer on top of a helper column, and to treat the helper column as the real output. If the comparison is one you run repeatedly and the result has to be handed to somebody else, the difference list itself becomes the deliverable — which is what the Piloteq Automate comparison workflow produces: records with a status and a written reason, rather than colour.
If you would rather not build this by hand
Get a difference list you can filter, not just colour
Piloteq Automate returns the comparison as records with a status and a reason, so the differences are a list you can sort, export and explain — rather than a fill colour on a sheet.
- ✓Missing records listed separately for each side
- ✓Value comparison with a tolerance you set
- ✓Duplicate detection across both files
- ✓Exact case-sensitive matching where you need it
Frequently asked questions
How do I highlight cells that are different in Excel?+
Select the range, open Home, then Conditional Formatting, then New Rule, and choose Use a formula to determine which cells to format. Enter a formula such as =$A2<>$B2 to highlight rows where column A differs from column B, then choose a fill colour. The critical detail is the reference style: the column letters must be absolute with a dollar sign and the row number must be relative, so the rule shifts down the range as it is applied.
Why does my conditional formatting rule highlight every cell?+
Almost always because of the relative reference. If the formula uses =$A2<>$B2 but was entered against a range starting at row 1, or uses A2 instead of $A2, the rule compares the wrong cells as it is copied down. The second common cause is the range — the Applies To range must start on the same row as the first row in your formula.
How do I highlight values in one column that do not exist in another column?+
Use a COUNTIF rule: =COUNTIF($D:$D, $A2)=0 will highlight every value in column A that does not appear anywhere in column D. This is the formatting version of the missing-records question, and it is the rule most commonly used for comparing two lists. Note that it is not case-sensitive — for invoice numbers and codes, use EXACT instead.
How do I highlight only the duplicate and not the original value?+
The usual rule, =COUNTIF($A:$A, $A2)>1, highlights every instance, including the first. To highlight only the second and later occurrences, expand the range as the row increases: =COUNTIF($A$2:$A2, $A2)>1. The expanding range means the first occurrence is never counted and every subsequent one is, which is what you want when you are deciding which row to delete.
Is conditional formatting case-sensitive?+
No. The comparison operators and COUNTIF treat ABC and abc as the same value. If case matters — which it does for employee codes, some invoice sequences and PAN-style identifiers — use the EXACT function in the rule instead, for example =NOT(EXACT($A2,$B2)) to highlight values that differ in any way including case.
Should I use conditional formatting or a helper column to find differences?+
For a quick visual check on a small range, formatting is faster. For anything you need to filter, sort, count, copy or explain, use a helper column with the same formula and apply formatting to that column. A formatting rule changes only the appearance of a cell — the cell value is unchanged, so you cannot filter on it, count it, or carry it into a pivot table.