Check an email list for typos and duplicates in Google Sheets™
A duplicate gets the same email twice and reports you as spam. A typo like jo@gmial.com bounces, and enough bounces damage your sending reputation. Both are easy to miss by eye and easy to find with a few formulas.
Duplicates that don't look like duplicates
Jo.Smith@Example.com, jo.smith@example.com (trailing space) and jo.smith@example.com are the same mailbox, but Data → Data cleanup → Remove duplicates treats them as different. Normalise first:
- Add a helper column:
=LOWER(TRIM(C2)). - Flag repeats:
=COUNTIF($D$2:$D, D2)>1. - Filter to TRUE and decide row by row which to keep — usually the one with more details filled in.
Addresses pasted from web pages can also carry invisible characters (non-breaking or zero-width spaces) that TRIM does not remove.
Typo'd domains
Look at the domains, not the whole addresses. Extract them with =LOWER(REGEXEXTRACT(C2,"@(.+)$")), then make a pivot table of that column. Real domains appear many times; typos like gmial.com, hotmial.co.uk, yaho.com and endings like .con or .co (for .co.uk) stand out as ones and twos.
Be careful: gmx.com, ymail.com and mail.com are real, and some organisations use unusual domains. Only fix a domain you are sure about, and ideally confirm with the person.
Also worth removing
- Addresses that are obviously not valid: no
@, two@s, spaces inside. - People who unsubscribed — keep them on a separate tab and exclude them every time.
- Test and placeholder addresses like
test@orexample.com.
Or check it in one click
Mail Merge Pre-flight Check finds duplicates after normalising, likely domain typos (and never “corrects” one real domain into another), invalid addresses and unsubscribes, and every suggestion is yours to accept or not. Checking is free.
Mail Merge Pre-flight Check checks your list and template in one click. Checking is free.
Mail Merge Pre-flight Check is not affiliated with Google or with any merge tool named on this page. Names are used only to identify them.