Find, highlight and count duplicates in Excel and Google Sheets with a formula

A duplicate is a value that COUNTIF finds more than once. Everything else — the colour, the 'only the second one' rule, the distinct count — is built on that one comparison. This is how we do it in our own workbooks, in forms that open the same in Excel 2016, Google Sheets and Numbers.

Contents

The one comparison

helper column: is this value repeated anywhere in the list?

=COUNTIF($A$2:$A$500, A2) > 1          TRUE / FALSE
=IF(COUNTIF($A$2:$A$500, A2) > 1, "dup", "")

$A$2:$A$500 is anchored so every row looks at the same list; A2 is not, so each row looks up its own value. Fill it down and you have a column you can filter, sort or count. Use a bounded range rather than A:A — it keeps Google Sheets fast and stops an empty cell far below from matching other empty cells.

Colour them

The same comparison as a conditional-formatting rule. Select the list from the top-left cell, add a rule with a custom formula and write it for that first cell:

Excel: Home → Conditional Formatting → New Rule → Use a formula · Sheets: Format → Conditional formatting → Custom formula is

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

Write the reference for the first selected cell and the app shifts it for the rest. If the rule colours the wrong cells, that’s almost always the mistake: the formula was written for A5 while the selection started at A2.

Numbers has no formula-based highlighting rules, so there the helper column is the highlight: keep it, and colour it with a text rule on “dup”.

Only the second, third… occurrence

Colouring every copy is right for finding; for cleaning you usually want the first one left alone and the repeats marked. Anchor the start of the range and let the end move:

running count: marks the second occurrence onwards

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

In row 2 this counts A2 in A2:A2 (once — not marked); in row 40 it counts the value in A2:A40, so the second time a name appears it turns TRUE. The same trick, COUNTIF($A$2:A2,A2), is how our workbooks number the n-th occurrence of something without an array formula.

Count them

how many rows are duplicates, how many distinct values

=SUMPRODUCT(--(COUNTIF($A$2:$A$500, $A$2:$A$500) > 1))    rows that have a twin
=SUMPRODUCT(1 / COUNTIF(A2:A500, A2:A500))               distinct values — fails on blanks
=SUMPRODUCT((A2:A500<>"") / COUNTIF(A2:A500, A2:A500&""))  distinct values, blanks ignored

The distinct count works because each value contributes 1 / (its count): a name that appears three times adds ⅓ three times. Blank cells break the plain version with #DIV/0! — the third form appends "" to the criteria so blanks count as a value, then multiplies them out with (A2:A500<>""). Both are SUMPRODUCT rather than an array-entered formula, so they work in Excel 2016 and Numbers, where UNIQUE doesn’t exist.

Duplicates on two columns

A duplicate order is the same customer and the same date, not the same customer:

COUNTIFS with two keys

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

Or build a key column — =A2&"|"&B2 — and run the single-column forms on it. The key column is easier to read in a filter and is what our inventory sheet does for SKU + location.

Across two sheets

is this value also on the other sheet?

=COUNTIF(Archive!$A$2:$A$2000, A2) > 0

What makes it miss

Trailing spaces. "Smith " and "Smith" are different values. Wrap the list in TRIM when you paste it in, or compare TRIM(A2) — COUNTIF can’t take TRIM on its range, so a helper column =TRIM(A2) is the honest fix.
Case. COUNTIF is case-insensitive: ABC and abc are the same. If they shouldn’t be, =SUMPRODUCT(--EXACT($A$2:$A$500, A2)) > 1 is the case-sensitive version.
Numbers stored as text. 1001 and "1001" don’t match. If a column came from an export, =VALUE(A2) in a helper column or Data → Text to Columns fixes it in one go.
Wildcards. COUNTIF treats ? and * in the criteria as wildcards, so a value like A* matches more than itself. Rare, but it’s why part numbers with asterisks sometimes all light up.

Removing them

Once the formula has found them, both apps can delete the repeats: in Excel, Data → Remove Duplicates; in Google Sheets, Data → Data cleanup → Remove duplicates. Both keep the first occurrence and drop the rest, which is the same thing the running-count rule marked — so filter on that column first if you want to see what’s about to go.