How to Remove Duplicate Emails in Google Sheets

Google Sheets is where a lot of contact lists end up: sign-up form responses, a volunteer roster shared by three people, an event RSVP sheet everyone has been pasting into. Those lists collect duplicates fast, because the same person signs up twice, or types their email with a capital letter one time and a trailing space the next.

Sheets gives you two ways to deal with it. You can delete duplicates in place with a menu command, or you can leave the original alone and build a clean list next to it with formulas. This guide covers both, plus how to spot the duplicates that only look different.

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.

First, trim the stray spaces

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.

The menu method: 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 [email protected] and [email protected] 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.

The formula method: 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 [email protected] and [email protected] 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), " ")).

Flag duplicates with COUNTIF instead of removing them

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.

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.

Gmail dots and plus tags

Gmail ignores dots before the @, so [email protected] and [email protected] reach the same inbox. People also sign up as [email protected] 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.

After the duplicates: are these addresses real?

Form sign-ups bring their own problems beyond duplicates: typos like yahooo.com, joke entries, and throwaway addresses that stop working in a day. Our typo fixer catches misspelled domains, and our guide to stopping fake sign-ups helps at the source. Before an important send, verify your list with Veml: it checks each address's format, domain and mailbox (without emailing anyone) and also flags disposable addresses and general inboxes like info@. You can paste straight from your sheet.

The no-formula shortcut

If you just need a clean list fast, copy the email column and paste it into our free Remove Duplicates tool. It ignores capitals and spaces, can treat Gmail dots and +tags as one address, and runs entirely in your browser. Paste the result back into a new tab. If you're about to load the list into an email platform, our guide on preparing a CSV for import covers the next step.