How to Remove Duplicate Emails in Excel, Including the Ones It Misses

You exported your contacts, opened the file in Excel, ran Remove Duplicates, and it said it removed a handful of rows. Then you scroll down and still see maria.lopez@brightpath.com twice. What happened?

Excel only removes rows that are exactly the same once you account for capital letters. A stray space, an invisible character, or a Gmail address written two different ways is enough to make two copies of the same person look different. This guide shows the quick built-in method first, then a helper-column method that catches the duplicates Excel misses, and how to keep the best copy of each contact instead of whichever one happens to be on top.

Step zero: make a backup copy

Remove Duplicates deletes rows for good once you save and close the file. Before you touch anything, do one of these:

• Save a copy of the file. Use File > Save As (or Save a Copy, depending on your version) and add "original" to the name.
• Copy the sheet inside the workbook. Right-click the sheet tab at the bottom, choose Move or Copy, check Create a copy, and click OK.

Work on the copy. If something goes wrong, you still have the untouched original, and you can compare row counts before and after.

Why Excel misses some duplicates

Here is how Excel's built-in tools treat the most common lookalikes:

So capital letters are not really the problem in Excel. Spaces and Gmail variations are. Even so, it's worth lowercasing everything, because the next tool you import into (an email platform or a CRM) may not be so forgiving.

The quick method: Data > Remove Duplicates

If your list is fairly clean, the built-in tool is all you need.

1. Click any cell inside your list.
2. Go to the Data tab and click Remove Duplicates (it sits in the Data Tools group on Windows; on a Mac it's also on the Data tab, though the button layout differs a little between versions).
3. Make sure My data has headers is checked if row 1 holds column names.
4. Click Unselect All, then check only the email column. This is important. If you leave every column checked, Excel only removes rows where the name, phone and everything else also match, which is rarely what you want.
5. Click OK. Excel tells you how many duplicates it removed and how many unique values remain.

Excel keeps the first copy it finds, reading from the top, and deletes the rest. Keep that in mind; we'll use it to our advantage later.

Look before you delete: highlight duplicates

If you'd rather see the duplicates before removing anything, use conditional formatting:

1. Select the email column.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Pick a color and click OK.

Every email that appears more than once now lights up, including the first copy. To review them together, turn on Data > Filter, click the arrow on the email column, and choose Filter by Color. Sorting the column A to Z also puts each pair next to each other so you can compare the rows by eye.

This highlight has the same blind spots as Remove Duplicates, so the next method is the one that matters for messy lists.

The helper column that catches hidden duplicates

The fix is to build a cleaned-up copy of each email in a new column, then find duplicates using that column instead of the original. Say your sheet looks like this: A is Email, B is First name, C is Last name, D is Phone, E is Date added.

1. In F1 type Clean email. In F2 enter:
   =LOWER(TRIM(A2))
   TRIM removes spaces at the start and end (and squeezes repeated spaces in the middle), and LOWER turns everything into lowercase.
2. Double-click the small square at the bottom-right corner of F2 to copy the formula down the whole list.
3. In G1 type Count. In G2 enter:
   =COUNTIF($F$2:F2,F2)
   and fill it down the same way.

The Count column is a running tally. The first time an address appears it shows 1, the second time 2, and so on. Filter column G to hide the 1s, and you're looking at exactly the extra copies. Unlike the highlight, the original first copy stays unflagged.

If you suspect pasted text from websites or emails, use this version in F2 instead, which swaps the non-breaking space (character 160) for a normal one before trimming:

=LOWER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))

Catching Gmail dots and plus tags

Gmail ignores dots in the part before the @, so tom.reed@gmail.com and tomreed@gmail.com land in the same inbox. Gmail users also add tags like +deals to sort their mail. To match these up, add a Match key column in H2 that works from your cleaned email in F2:

=IF(RIGHT(F2,10)="@gmail.com",SUBSTITUTE(IFERROR(LEFT(F2,FIND("+",F2)-1),LEFT(F2,FIND("@",F2)-1)),".","")&"@gmail.com",F2)

For a Gmail address, it cuts everything from the + (or from the @ if there is no +), strips the dots, and puts @gmail.com back on. So tom.reed+deals@gmail.com becomes tomreed@gmail.com. Every other address passes through unchanged. Point your Count formula at column H instead of F when you use this.

Keep the newest or most complete row

Since Remove Duplicates keeps the top copy, you decide which copy survives by sorting first.

To keep the most recent entry:

1. Click inside the list and go to Data > Sort.
2. Sort by Date added, order Newest to Oldest. (If that option doesn't appear, your dates are stored as text and need fixing first.)
3. Run Data > Remove Duplicates with only your Clean email or Match key column checked.

To keep the most complete entry: add one more helper column, Filled, with =COUNTA(B2:E2). It counts how many of the detail columns have something in them. In the Sort box, sort by Filled, Largest to Smallest, then click Add Level and sort by Date added, newest first, as the tie-breaker. Now run Remove Duplicates on the key column. The row with the most details wins, and among equals, the newest wins.

Remove Duplicates works on the values your formulas show, so using a formula column as the key is fine.

Tidy up when you're done

Once the duplicates are gone:

• Decide which email to keep. Most people copy the Clean email column, then use Paste Special > Values over the original email column so the clean versions stay without the formulas.
• Delete the helper columns (Clean email, Match key, Count, Filled) so they don't end up in your email tool.
• Compare the row count with your backup. If the difference is much bigger than you expected, check that you didn't leave every column checked or check the wrong one.

Duplicates gone. Now check the addresses themselves

A deduplicated list can still be full of addresses that will never reach anyone: typos like gmial.com, old work addresses for people who changed jobs, and made-up entries from sign-up forms. Excel can't tell you any of that. Run the list through our typo fixer to catch misspelled domains, then check every address with Veml, which asks each mail server whether the mailbox actually exists, without sending an email. Every address comes back Passed, Risky, Unknown or Invalid with a plain-English reason, and since Veml removes duplicates before working out the price, you only pay for unique addresses.

The faster way for big or messy lists

If all this feels like a lot of formulas for one cleanup, paste the email column into our free Remove Duplicates tool. It trims spaces, ignores capitals, and has an option to treat Gmail dots and +tags as the same address. It runs in your browser, so nothing is uploaded. Then paste the clean list back into Excel, or download it.

Working in Google Sheets instead? The menus and formulas are a little different, so see our guide to removing duplicate emails in Google Sheets.