Find duplicates in a list with COUNTIF

Sample data. Emails in A, the count formula in B2 and the label formula in C2 are copied down.

The formula

Click the first Count cell (B2) and type:

=COUNTIF(A:A,A2)

B2 shows 2: ana@example.com is in the list twice. Copy it down. Every 1 is a value that appears once, and every number above 1 is a duplicate. Here ana and raj show 2.

• A:A is the column to search.
• A2 is the value to count, the email on this row.

Label the duplicates

Wrap the count in IF to show a word instead of a number:

=IF(COUNTIF(A:A,A2)>1,"Duplicate","")

Rows 2, 3, 5 and 7 show Duplicate and the rest stay blank. Filter the column on Duplicate to see them together.

Mark only the second copy

To delete repeats but keep the first copy of each, count only from the top of the list down to the current row:

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

Only rows 5 and 7 show Repeat, the second time ana and raj appear. The range grows as you copy down ($A$2:A2, then $A$2:A3, and so on), so the first copy never counts as a repeat. Filter on Repeat, delete those rows, and one copy of each email stays.

Count unique values

How many different emails are in A2:A8?

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

The answer is 5. How many extra copies are there?

=ROWS(A2:A8)-SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

That gives 2. Use a range with no empty cells: a blank cell in it gives #DIV/0!. In Excel 2021 and later, Microsoft 365 and Google Sheets, =UNIQUE(A2:A8) lists each email once, here 5 rows.

Remove or highlight them with the menus

Work on a copy of the sheet first. In Excel, select the list, then Data > Remove Duplicates. To colour them instead: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

In Google Sheets, select the list, then Data > Data cleanup > Remove duplicates, and choose the columns to compare. Google counts values that differ only in capitals as duplicates. To colour them: Format > Conditional formatting, pick "Custom formula is" and enter:

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

Two values look the same but are not counted

COUNTIF ignores capitals, so =COUNTIF(A2:A8,"ANA@EXAMPLE.COM") still finds 2. A space you cannot see is different: if H2 holds "kim@example.com " with a space at the end, this finds nothing:

=COUNTIF(A2:A8,H2)

It shows 0. TRIM removes the extra space before counting:

=COUNTIF(A2:A8,TRIM(H2))

Now it shows 1. To clean a whole column, put =TRIM(A2) in a spare column, copy it down, then paste the results back as values. COUNTIF, SUMPRODUCT and ROWS work in every version of Excel and in Google Sheets.

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.