How to Remove Duplicate Contacts in Excel

Before you start: decide what makes two contacts “the same”

Removing duplicates always compares something. For contacts that is usually the email address, because it's unique per person and it's what CRMs and email tools match on. If your list has no emails, use a phone number or a combination such as first name + last name + company.

Also decide which copy to keep. If one row has a phone number and the other doesn't, you want the more complete row — which Excel's built-in tool won't pick for you. Always work on a copy of the file.

Step 0: clean the key column first

Excel's duplicate tools ignore capital letters but not spaces, so “asha@example.com” and “asha@example.com ” (trailing space) count as two different contacts. Normalise the key in a helper column before you look for duplicates — lowercasing as well keeps the cleaned list consistent. With emails in column C, add a column with:

Fill the formula down, then use this helper column as the key in the methods below.

Method 1: Data → Remove Duplicates

1. Select any cell in your table.
2. Go to Data → Remove Duplicates.
3. Tick “My data has headers”, then untick every column except your key (the cleaned email column).
4. Click OK. Excel keeps the first occurrence of each value and deletes the rest.

It's fast, but it only ever keeps the first row and doesn't show which rows it deleted. Sort the table first if you want a particular row to come first (for example, newest date at the top).

Method 2: flag duplicates with COUNTIF, then review

Flagging lets you review before deleting. With the cleaned key in column H, add a column with =COUNTIF($H$2:H2,H2)>1 and fill it down. The first occurrence shows FALSE and every repeat shows TRUE. Filter the column to TRUE, check the rows, then delete them.

To see all copies of a duplicated contact (including the first), use =COUNTIF($H$2:$H$5000,H2)>1 instead, and sort by the key so copies sit next to each other.

Method 3: UNIQUE() in Excel 365

In Excel 365 and Excel 2021+, =UNIQUE(A2:F5000) returns a de-duplicated copy of a range on a new sheet without touching the source. It compares entire rows, so it only removes rows that are identical in every column — clean spaces and capitals first, or it will miss near-duplicates.

Method 4: without formulas

If you do this regularly, TheSpreadFix's duplicate finder does the cleaning and the comparison in one step: it ignores capitals and extra spaces, lets you match on one or several columns, keep the first, last or most complete row, and shows every row it will remove before you download. The file is processed in your browser.

Common mistakes

• Selecting all columns in Remove Duplicates — two rows for the same person with different phone numbers won't be treated as duplicates.
• Treating blank emails as duplicates — contacts without an email are different people, not copies.
• Deduplicating before trimming spaces — duplicates hide behind trailing spaces.
• Working on the only copy of the file — keep the original until you've checked the result.