Home › Excel basics › Remove duplicates

How to Find, Highlight and Remove Duplicates in Excel

Duplicates creep in when lists are merged, exported twice or typed by hand. Excel gives you several ways to deal with them, from simply highlighting them to deleting them in one click.

Excel CSE · 2 min read

Step 1: highlight duplicates first

Before deleting anything, see what you're dealing with:

1. Select the column, for example the email addresses.
2. Go to Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values.
3. Click OK. Every value that appears more than once is colored.

Combine this with a filter by color (Ctrl+Shift+L) to see only the duplicates. More in conditional formatting.

Flag duplicates with a formula

A helper column tells you exactly how often each value occurs:

A result of 2 or more means it's a duplicate. To mark only the second and later occurrences (so you can keep the first), use an expanding range:

See COUNTIF for more.

Delete duplicates with Remove Duplicates

1. Click inside your data.
2. Go to Data › Remove Duplicates.
3. Tick the columns that together define a duplicate. Tick only "Email" to treat rows with the same email as duplicates; tick all columns to remove only completely identical rows.
4. Click OK. Excel reports how many duplicates it removed and how many unique values remain.

List unique values without deleting anything

In Microsoft 365 and Excel 2021+, the UNIQUE function creates a live list without duplicates and leaves your data untouched:

Use =UNIQUE(A2:C500) for unique rows across several columns. See UNIQUE.

Duplicates that Excel doesn't see

"Anna Jansen" and "anna jansen " look like duplicates to you, but the trailing space makes them different to Remove Duplicates. Clean the data first:

• Remove extra spaces with TRIM.
• Make capitalization consistent with UPPER, LOWER or PROPER. (Remove Duplicates itself ignores case.)