Highlight Duplicates
Two rules colour duplicates and they answer different questions: one marks every occurrence, the other only the surplus copies. The built-in Duplicate Values rule is the first — a value appearing three times shades all three cells, because the rule has no notion of an original. New Rule > Use a formula with =COUNTIF($A$2:A2,A2)>1 is the second, leaving the earliest occurrence plain, and it is the one to reach for when the coloured cells are the ones you intend to delete. Neither writes anything into the sheet: no column appears, no row is removed, and a colour is not a value, so COUNTIF and SUMIF cannot see it. Filter by Color at least isolates the flagged rows on screen.
The formula
=COUNTIF($A$2:A2, A2)>1 New Rule > Use a formula: colours the 2nd and later copies only
=COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2)>1 duplicate ROWS: apply to $A$2:$F$500 so the whole row colours
=SUMPRODUCT(--EXACT($A$2:$A$500,A2))>1 case-sensitive, unlike every built-in rule
=COUNTIF(Sheet2!$A$2:$A$500, A2)>0 colour what also appears on another sheet
=AND(A2<>"", COUNTIF($A$2:$A$500,A2)>1) keeps empty cells from colouring each otherA worked example
A2:A501 holds 500 invoice numbers. INV-2041 appears three times, on rows 12, 88 and 301; nothing else repeats.
Select A2:A501 > Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values > Light Red Fill > OK
All three cells turn red, rows 12, 88 and 301 alike, because the rule flags every member of a repeated set and has no notion of an original. Delete that rule and build New Rule > Use a formula to determine which cells to format with =COUNTIF($A$2:A2,A2)>1, and rows 88 and 301 colour while row 12 stays plain — the version to use when the coloured cells are the ones you mean to remove.
Which one do I need?
Frequently asked questions
Does highlighting duplicates change my data?
No. A conditional-formatting rule stores no result and touches no cell: it re-tests its condition whenever the range changes and colours whatever currently matches. Clear the rule and the sheet is byte for byte what it was. That is also the limit of it — there is no value behind the colour for a formula to count, so a highlight can be looked at but not calculated with.
Why are both copies highlighted instead of just the duplicate?
The Duplicate Values rule marks every cell whose value occurs more than once, so a value appearing three times colours three cells. It has no idea which one came first. For the surplus copies alone use New Rule > Use a formula and =COUNTIF($A$2:A2,A2)>1, whose expanding range only ever looks at the rows above the cell being tested.
How do I highlight duplicate rows rather than duplicate cells?
Select the whole block — A2:F500, say — then New Rule > Use a formula with =COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2)>1, adding one range and criteria pair for every column that makes up the key. Lock the column letters but not the row numbers, so $A2 rather than $A$2: each row then tests its own values and the entire row colours together.
Why is the highlight landing on the wrong rows?
A formula rule is written from the viewpoint of the active cell in the selection, and Excel offsets it for every other cell in the range. Select A2:A500 while A1 happens to be the active cell and the whole rule reads one row too high. Check Manage Rules > Applies to, note which cell the range starts at, and write the formula relative to that cell.
New guides and tools, once a month
DE + EN · double opt-in · no spam