How to find and remove duplicate emails in Excel and Google Sheets

Excel and Google Sheets both have a one-click duplicate remover. Both miss the same kinds of hidden duplicates. Here is how to use the built-in tools, and the helper formulas that catch the rest.

By David Ray · Updated November 5, 2026 · 13 min read · How we research

Before you start: make a backup copy

Excel's 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 spreadsheets miss some duplicates

Here is how Excel's built-in tools treat the most common lookalikes. Google Sheets' Remove duplicates menu behaves the same way: it also ignores capital letters but not spaces or Gmail variants.

So capital letters are not really the problem for either program's menu tool. 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.

One exception: the Google Sheets UNIQUE formula does treat capitals and spaces as different, which is why the formula method below cleans the addresses first.

Excel: 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.

Excel: highlight duplicates before you delete

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.

Excel: 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.

Excel: 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.

Excel: 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.

Google Sheets: delete in place, or build a clean copy?

Pick your approach before you start. Here's how they compare:

If the sheet is fed by a Google Form, the formula approach is usually the better choice, because you shouldn't delete rows from a responses sheet that other people rely on.

Google Sheets: trim the stray spaces first

Extra spaces are the most common reason two identical-looking emails don't count as duplicates. Sheets has a one-click fix:

1. Select the email column (click the column letter).
2. Go to Data > Data cleanup > Trim whitespace.

This removes spaces at the start and end of each cell, and repeated spaces in between. Google notes one exception: non-breaking spaces, the kind that sneak in when you paste from a web page, are not trimmed. If a pair still won't match after trimming, that's a likely cause, and the formula method below handles it.

Google Sheets: Data > Data cleanup > Remove duplicates

1. Select the whole range of your list, including every column you want to keep together (for example A1:E500), so the names and phone numbers stay with their emails.
2. Go to Data > Data cleanup > Remove duplicates.
3. Check Data has header row if row 1 holds column names.
4. Under the columns to analyze, uncheck Select all and check only the email column.
5. Click Remove duplicates. Sheets reports how many duplicate rows it found and removed.

According to Google, this tool treats cells as duplicates even when the letter case differs, so Sam@Northwind.com and sam@northwind.com count as one. It keeps the first row from the top and removes the later ones.

Keep the latest sign-up, not the first one

With form responses, the first row for a person is their oldest answer. If someone signed up again to update their phone number, you probably want the newer row. Sort before you remove duplicates:

1. Select the full range, including the header row.
2. Go to Data > Sort range > Advanced range sorting options.
3. Check Data has header row, choose the Timestamp column, and pick Z → A so the newest is on top.
4. Now run Data > Data cleanup > Remove duplicates on the email column as above.

Do this on a duplicated tab rather than the live responses sheet, and the original form data stays safe.

Google Sheets: a clean list with UNIQUE

On a new tab, the simplest formula is:

=UNIQUE(Responses!B2:B)

(Replace Responses!B2:B with the sheet name and column that hold your emails.) The catch: unlike the menu tool, UNIQUE pays attention to capitals and spaces, so Sam@Northwind.com and sam@northwind.com both survive. You need to clean the values first.

A first attempt might be =UNIQUE(ARRAYFORMULA(LOWER(TRIM(Responses!B2:B)))). That lowercases and trims every address, but because B2:B runs to the bottom of the sheet, all the empty rows turn into one blank entry in your results. This version filters out the blanks first:

=UNIQUE(FILTER(LOWER(TRIM(Responses!B2:B)), TRIM(Responses!B2:B)<>""))

Reading it from the inside out: TRIM removes stray spaces, LOWER makes everything lowercase, FILTER drops the empty rows, and UNIQUE keeps one copy of each address. Because it reads the whole column, new form responses show up in your clean list automatically. Want it alphabetical? Wrap it in SORT: =SORT(UNIQUE(FILTER(LOWER(TRIM(Responses!B2:B)), TRIM(Responses!B2:B)<>""))).

If non-breaking spaces are causing trouble, swap each TRIM(Responses!B2:B) for TRIM(SUBSTITUTE(Responses!B2:B, CHAR(160), " ")).

Google Sheets: flag duplicates with COUNTIF

Sometimes you want to see who signed up twice rather than silently dropping them. Say emails are in column B of your working tab.

1. In an empty column, say F, put the header Clean in F1 and this in F2:
   =ARRAYFORMULA(IF(B2:B="", "", LOWER(TRIM(B2:B))))
   One formula fills the whole column and keeps working as new rows arrive.
2. In G1 put Times seen and in G2:
   =IF(F2="", "", COUNTIF(F$2:F2, F2))
   Then drag the formula down. A 1 means first appearance; 2 or more means a repeat.

For a column that fills itself as responses come in, use this single formula in G2 instead:

=ARRAYFORMULA(IF(F2:F="", "", COUNTIFS(F2:F, F2:F, ROW(F2:F), "<="&ROW(F2:F))))

It counts, for each row, how many times that email has appeared up to and including that row. Use Data > Create a filter and hide the 1s to see only the repeats.

Google Sheets: highlight duplicates with a custom formula

Sheets doesn't have a ready-made "duplicate values" rule, but a custom formula does the job:

1. Select the email column, for example B2:B.
2. Go to Format > Conditional formatting.
3. Under Format cells if, choose Custom formula is.
4. Enter =AND($F2<>"", COUNTIF($F$2:$F, $F2)>1) to highlight every copy of a duplicate, or =AND($F2<>"", COUNTIF($F$2:$F2, $F2)>1) to highlight only the second and later copies. The $F2<>"" part stops empty rows from lighting up.
5. Choose a fill color and click Done.

Both formulas look at the Clean column from the previous step, so differences in capitals and spaces don't hide anything. The dollar signs matter: $F$2 stays fixed while $F2 moves down one row for each cell being checked. Note that Google's custom formulas can only refer to the same sheet, so keep the Clean column on the tab you're formatting.

Google Sheets: Gmail dots and plus tags

Gmail ignores dots before the @, so sam.ortiz@gmail.com and samortiz@gmail.com reach the same inbox. People also sign up as samortiz+club@gmail.com to filter their mail. Sheets' REGEXREPLACE makes a match key for these in one line. With a cleaned email in F2:

=IF(RIGHT(F2,10)="@gmail.com", SUBSTITUTE(REGEXREPLACE(F2, "(\+.*)?@gmail\.com$", ""), ".", "")&"@gmail.com", F2)

For Gmail addresses it removes the +tag and the domain, strips the dots, and adds @gmail.com back. Everything else is left as is. Run your COUNTIF or Remove duplicates on this key column instead of the plain email.

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. Neither Excel nor Google Sheets can tell you any of that, and sign-up forms add joke entries and throwaway addresses too. Run the list through ListaFix's 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.

To stop junk at the source, see how to stop fake and bot signups.

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 ListaFix's 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 Sheets, or download it.

If you're about to load the list into an email platform, our guide on preparing a CSV for import covers the next step.

Questions people ask

Is Excel's Remove Duplicates case-sensitive?

No. Excel treats Maria@BrightPath.com and maria@brightpath.com as the same value, so capital letters alone won't stop it from finding a duplicate. Extra spaces and invisible characters will, which is why the LOWER(TRIM()) helper column helps.

Does Google Sheets' Remove duplicates care about capital letters?

No. Google says cells with identical values but different letter case are treated as duplicates by Data cleanup > Remove duplicates. The UNIQUE function is different and does treat capitals as different, which is why the formula version lowercases everything first.

Which copy does Remove Duplicates keep?

It keeps the first copy it finds from the top of the list and deletes the ones below. Sort the list first, for example newest date at the top, to control which copy survives.

Can I undo Remove Duplicates?

You can press Ctrl+Z (Cmd+Z on a Mac) right away, but once the file is saved and closed the rows are gone. That's why it's worth saving a backup copy of the file or duplicating the sheet before you start.

Why does my COUNTIF formula say there are no duplicates when I can see two copies?

Usually one copy has a space or a non-breaking space somewhere. Point COUNTIF at a cleaned column built with =LOWER(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) instead of the raw email column.

Will Remove duplicates break my Google Form responses?

Deleting rows from the responses sheet changes the data other people may be looking at. It's safer to duplicate the tab and clean the copy, or use a UNIQUE formula on a separate tab that reads from the responses.

Why does my UNIQUE list show a blank row?

Because an open-ended range like B2:B includes all the empty rows below your data, and UNIQUE keeps one copy of the blank. Wrap the range in FILTER with a condition like TRIM(B2:B)<>"" to leave the blanks out.

Can I see who signed up more than once instead of deleting them?

Yes. Add a COUNTIF running count next to a cleaned email column. Any row showing 2 or more is a repeat, and you can filter or highlight those rows to review them.