The short answer
To remove duplicate emails in Google Sheets, first clean the addresses with =LOWER(TRIM(A2)) so capital letters and stray spaces don't hide duplicates. Then select your data, choose Data › Data cleanup › Remove duplicates, check only the email column, and click Remove duplicates. Sheets keeps the first copy of each address and deletes the rest.
That's the fast way. Below are four methods, from deleting duplicates outright to just highlighting them so you can decide row by row, plus notes on which copy to keep.
Make a backup first. Remove duplicates deletes rows. Right-click the sheet tab and choose Duplicate so you have an untouched copy to go back to.
Step 1: normalize the addresses
To a person, Jane@X.com (with a trailing space) and jane@x.com are the same address. To a spreadsheet they are two different pieces of text. If you dedupe without cleaning first, both copies can survive.
In an empty column next to your emails (say column B), enter this in B2:
=LOWER(TRIM(A2))
Drag it down the column. TRIM removes spaces at the start and end. LOWER turns every letter lowercase. In practice, mail providers don't treat a capital letter as a different address, so lowercasing is safe for deduping.
Now replace the originals. Copy column B, click A2, and paste as values only with Ctrl+Shift+V (Cmd+Shift+V on a Mac). Then delete column B. Column A now holds clean addresses, and every method below compares like with like.
Method 1: Remove duplicates (deletes rows)
This is the built-in tool, and the quickest way to end up with one row per address.
- Select the whole table, not just the email column. If you select only column A, the other columns aren't touched and names can end up next to the wrong address.
- Choose Data › Data cleanup › Remove duplicates.
- Check Data has header row if row 1 holds your headers.
- Under Columns to analyze, check only the email column. If every column stays checked, two rows count as duplicates only when every cell matches. "Jane, Acme, jane@acme.com" and "Jane Doe, Acme, jane@acme.com" would both stay.
- Click Remove duplicates. Sheets tells you how many duplicate rows it removed and how many unique rows remain.
Sheets keeps the first copy of each address, counting from the top. If that matters, read which row to keep before you run it.
Method 2: UNIQUE (keeps the original intact)
If you'd rather not delete anything, build a deduplicated list with a formula. In a new tab or an empty column, enter:
=UNIQUE(A2:A)
It returns each address once, in the order it first appears. To skip the blank cells at the bottom of the column, wrap the range in FILTER:
=UNIQUE(FILTER(A2:A, A2:A<>""))
UNIQUE also works on several columns. =UNIQUE(A2:C) returns rows where the combination of all three columns is unique. That has the same catch as leaving every column checked in Remove duplicates: two rows with the same email but different names both come through.
The result is live. Change the source and the list changes with it. When you're happy with it, copy it and paste as values only so you can sort and edit it freely.
Method 3: highlight duplicates with conditional formatting
Highlighting is the best choice when duplicate rows carry different details and you want to look before you delete.
- Select the email range, for example
A2:A. - Choose Format › Conditional formatting.
- Under Format rules, open the Format cells if menu and choose Custom formula is.
- Enter the formula below, pick a fill color, and click Done.
=COUNTIF($A:$A,$A2)>1
Every address that appears more than once is now colored, including the first copy. Sort the sheet by the email column to put the copies next to each other. With a filter turned on (Data › Create a filter), you can also filter the column by fill color to see only the duplicates.
Method 4: count duplicates
To see how big the problem is before changing anything, add a helper column. In C2 (or any empty column), enter:
=COUNTIF($A$2:$A2,$A2)>1
Drag it down. The range grows by one row each time, so the first copy of an address shows FALSE and every later copy shows TRUE. Then count the extra copies in any empty cell:
=COUNTIF(C:C,TRUE)
That number is how many rows Remove duplicates would delete, as long as you checked only the email column. It's also a handy filter: show only TRUE in column C and you're looking at exactly the rows that would go.
Which row to keep
When your sheet holds only emails, any copy is as good as another. When rows also carry names, companies, sign-up dates or notes, the copies can differ, and Remove duplicates simply keeps the topmost one.
- Sort first so the row you want sits on top. For example, sort by date, newest first, to keep the latest record.
- Merge by hand when both copies hold something useful, like a phone number in one and a company in the other. Find them with Method 3 or 4, combine the details into one row, then delete the other.
- Carry over opt-outs. If any copy is marked unsubscribed, make sure the row you keep says so too.
Advanced: Gmail dots and plus addresses
Gmail ignores dots in the part before the @, and anything after a plus sign. So jane.doe@gmail.com, janedoe@gmail.com and jane.doe+news@gmail.com all reach the same Gmail inbox.
It's tempting to strip those out with a formula. Be careful:
- This is a Gmail rule. Other providers can treat a dot as part of the address, so removing it can turn a working address into a different one.
- People use plus addresses on purpose, often to sort their mail. If someone signed up as
jane+news@gmail.com, that's the address they gave you.
A safer approach is to flag likely Gmail duplicates for a human to review and leave the addresses as they were entered.
Next step: verify what's left
Removing duplicates makes a list shorter, not cleaner. A deduplicated list can still hold typos, abandoned mailboxes and addresses at companies that no longer exist. Those bounce, and bounces hurt your sender reputation. (Hard vs. soft bounces explains the difference.)
The Email Verification add-on checks each address by asking the recipient's mail server whether it would accept mail for it. Nothing is sent to the person, and a status lands in column B next to each email. It charges one credit per address, so an address you check twice costs twice. Dedupe first, then verify.
For the full walkthrough, see how to verify emails in Google Sheets. For the rest of the cleanup, see how to clean an email list. Getting ready to send from your own account? Read verify your email list before a mail merge.
FAQ
Does Remove duplicates keep the first or the last copy?
The first. Google Sheets keeps the topmost copy of each address and deletes the copies below it. If you'd rather keep a different row, sort the sheet so that row comes first, then remove duplicates.
Why didn't Remove duplicates catch all my duplicates?
Usually because the copies aren't exactly identical. A trailing space or a stray capital letter can keep two copies of the same address from matching. Clean the column with =LOWER(TRIM(A2)) first, paste the results back as values, and run it again. Also make sure only the email column is checked under Columns to analyze.
Can I find duplicates without deleting anything?
Yes. Highlight them with a conditional formatting rule using =COUNTIF($A:$A,$A2)>1, or build a separate deduplicated list with =UNIQUE(A2:A). Neither one changes your original rows.
Should I remove duplicates before verifying my list?
Yes. The Email Verification add-on charges one credit per address, so checking the same address twice costs two credits. Dedupe first and you pay for each address only once.
Deduped and ready to check? Install Email Verification free and verify your first 50 addresses, no card needed.