Remove Duplicates in Excel: 4 Methods (With Formulas)

Duplicate rows inflate totals and send two emails to the same customer. Excel offers several ways to find and remove them. Pick based on whether you want to delete, flag, or create a clean copy.

1. The Remove Duplicates button

1. Click inside the data.
2. Data > Remove Duplicates.
3. Tick the columns that define a duplicate (e.g. just Email, or Name + Date).
4. OK. Excel reports how many it removed.

It deletes rows permanently, so work on a copy.

2. UNIQUE (Excel 365, Google Sheets)

=UNIQUE(A2:C500)

Creates a clean list elsewhere and leaves the original alone. Updates automatically. See UNIQUE.

3. Flag duplicates with COUNTIF

Next to the data, in D2:

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

TRUE marks every value that appears more than once. Filter on TRUE to review.

To flag only the second and later copies (keep the first):

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

Note the half-locked range, the same trick as a running total.

4. Highlight duplicates

Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Good for eyeballing before you delete anything.

Why duplicates survive

• Extra spaces: "Sam Lee" vs "Sam Lee ". Clean with TRIM first.
• Different case: Remove Duplicates ignores case, but some other tools don't.
• Numbers vs text: "1042" stored as text isn't the number 1042.

Count duplicates

Total distinct values: =COUNTA(UNIQUE(A2:A500)). Number of duplicate rows: =COUNTA(A2:A500)-COUNTA(UNIQUE(A2:A500)).

Frequently asked questions

How do I remove duplicates in Excel?

Data > Remove Duplicates, choosing which columns define a duplicate.

How do I find duplicates without deleting them?

Use conditional formatting or =COUNTIF($A$2:$A$500,A2)>1.

Does Remove Duplicates keep the first occurrence?

Yes, it keeps the first and removes later ones.

How do I remove duplicates in Google Sheets?

Data > Data cleanup > Remove duplicates, or =UNIQUE(range).