How to Highlight Duplicates Without Deleting Them

Select your data, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. That takes ten seconds and is usually not what you want.

It colours every copy, including the first one — so a list with three entries of the same reference shows three red cells, and nothing tells you which one you meant to keep. This formula colours only the repeats:

=COUNTIF($A$2:$A2, $A2)>1

The same fifteen references, two rules. Seven cells coloured on the left, four on the right — and only the right-hand version tells you which copies to look at.

The trick, in one line

Look carefully at the range: $A$2:$A2. The start is locked, the end is not.

On row 2 that range is A2:A2 — one cell. On row 8 it's A2:A8. On row 40, A2:A40. The range grows as the rule moves down the column, so each row only ever looks at itself and everything above it.

Which means the first occurrence of a value counts 1 and isn't flagged. The second counts 2 and is. That's the entire mechanism.

Formula	What it colours
=COUNTIF($A$2:$A$16, $A2)>1	Every copy, first included — the built-in rule
=COUNTIF($A$2:$A2, $A2)>1	Only the second and later copies
=COUNTIF($A$2:$A$16, $A2)=1	Only values that appear exactly once

Setting it up

1. Select the data, starting from the first data row. Not the header.
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Paste the formula, written as if for the first selected cell.
4. Click Format, pick a fill, and confirm.

⚠️ Step 3 is where this goes wrong for most people. The formula must be written for the top-left cell of your selection. If your data starts on row 2, every 2 in the formula is right. If it starts on row 5, the formula is =COUNTIF($A$5:$A5, $A5)>1. Excel then shifts it for every other cell itself. Write it for row 2 while your selection starts on row 5 and the colours land one place off, which looks like the rule is broken when it's doing exactly what you asked.

Making the count visible

While you're setting this up, put the same expression in a spare column as a plain formula:

=COUNTIF($A$2:$A2, $A2)

You get 1, 1, 1, 2, 1, 1, 2, 1, 1, 3 — an occurrence number for each row. First time seen, second time seen, third.

Two reasons this is worth the column. It proves the rule is doing what you think before you trust it, and the numbers are useful in themselves — filter to greater than 1 and you have every extra copy in a list, ready to review or remove.

Colouring the whole row

Highlighting one cell is fine for a single column. When there are twelve columns and the duplicate is in column C, you want the row.

Select all the columns, then use the same formula with the column locked:

=COUNTIF($C$2:$C2, $C2)>1

The dollar before C is what does it. Without it, the test slides across as Excel evaluates each column, so column D gets tested against column D's values and the row colours in pieces.

Column locked, row free — that's the anchoring for any whole-row rule, and getting it wrong is the single most common conditional formatting complaint there is.

When the duplicate is the whole row

Sometimes one repeated reference is fine and what you're actually looking for is the same record entered twice — same name, same date, same amount.

=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1

Every range takes the same expanding treatment. A row is flagged only when all three values have appeared together before.

Add or remove pairs to match the columns that define a unique record. Don't be tempted to join the values into one string with & first — "AB"&"C" and "A"&"BC" produce the same text, which invents duplicates that aren't there.

Duplicates Excel won't see

Different case

COUNTIF treats abc-100 and ABC-100 as the same value, and so does the built-in rule. Usually helpful. When it isn't:

=SUMPRODUCT(--EXACT($A$2:$A2, $A2))>1

EXACT compares character by character and respects case. The double minus turns its TRUE/FALSE into 1s and 0s so SUMPRODUCT can add them.

Trailing spaces

"ORD-4402 " is not "ORD-4402", so neither will be flagged. If a duplicate you can see isn't colouring, check =LEN(A2) against the other one. TRIM in a helper column fixes the list properly; wrapping TRIM into the rule only hides the problem.

Long numbers that aren't actually duplicates

This one is worth knowing because it produces false positives on real data. Excel stores numbers to fifteen significant digits. Anything longer is rounded for storage, so two sixteen-digit account or card numbers differing only in the last digit become identical as far as Excel is concerned — and get flagged as duplicates.

The fix is to store long identifiers as text, not numbers. Format the column as Text before the data goes in, or import with that column set to Text. Once the digits are gone they aren't recoverable from the file.

Does this work in Google Sheets?

Yes. Format → Conditional formatting → Custom formula is, and the same formulas apply unchanged, expanding range included.

Sheets also has COUNTUNIQUE for a quick count of distinct values, which Excel has no direct equivalent for.

When it goes wrong

Everything is coloured

The range in the formula isn't anchored — A2:A2 instead of $A$2:$A2. Every row then counts only itself, and the test is meaningless.

Nothing is coloured

Usually the opposite anchoring: $A$2:$A$2, both ends locked, so every row tests the same single cell. Check with F4 — it cycles through the four combinations.

The colours are offset by a row or two

The formula was written for a different starting row than the selection actually begins on. Delete the rule and redo it with the selection in place.

Only some columns colour on a whole-row rule

Missing $ before the column letter.

It worked, then stopped after I sorted

Expanding-range rules depend on row order — "seen before" means "appears higher up". Sorting changes which copy is first. That's correct behaviour, but if you need a stable answer, put the count in a real column and convert it to values before sorting.

Common questions

How do I get rid of them once I can see them?

Carefully, and not with Remove Duplicates until you've looked at what you'd be losing — the safe method is here. Highlighting first exists precisely so that deleting is an informed decision.

Can I find duplicates between two separate lists?

That's a different question with a different formula — you're comparing across lists rather than within one. Matching two lists is covered here.

Can I highlight the first occurrence instead of the later ones?

Yes: =COUNTIF($A$2:$A2, $A2)=1 combined with a check that it appears more than once overall. In practice a Times seen column is clearer than a second rule.

Why is it slow on a big sheet?

Every row runs a COUNTIF over a range that keeps growing, so the work rises sharply with row count. Past a few thousand rows, use a real helper column and paste it as values rather than a live conditional formatting rule.

Can I count duplicates without highlighting anything?

Yes — that's just =COUNTIF($A$2:$A$16, $A2) in a column, no formatting involved. COUNTIF in full is here.

The short version

• The built-in rule colours every copy. That's its only real limitation.
• =COUNTIF($A$2:$A2, $A2)>1 colours only the repeats. Lock the start, leave the end free.
• Write the formula for the first cell of your selection, not for row 2 out of habit.
• Lock the column with $ to colour whole rows.
• COUNTIFS with several expanding ranges finds duplicate records rather than duplicate cells.
• Trailing spaces and case differences hide duplicates; sixteen-digit numbers invent them.
• Put the count in a column while you build it — you'll trust the rule, and the numbers are useful.

Next steps: once you can see them, removing duplicates without losing data is the follow-on, and the counting underneath all of this is COUNTIF and COUNTIFS. For duplicates spread across two different lists rather than within one, start here instead.