How to Clean an Email List in Excel: Duplicates, Typos and Formatting

Key takeaways

• Always work on a copy and keep the original file untouched.
• TRIM, LOWER and SUBSTITUTE fix most formatting problems.
• Remove Duplicates works only after addresses are normalized.
• Formulas can flag obvious format errors and common domain typos.
• Excel cannot check whether mailboxes exist; finish with a verification tool.

In this guide

1. Prepare your workbook
2. Step 1: Fix formatting
3. Step 2: Flag invalid formats
4. Step 3: Extract the domain
5. Step 4: Flag common typos
6. Step 5: Find and remove duplicates
7. Step 6: Remove addresses you should not email
8. Step 7: Verify the mailboxes
9. Step 8: Build import-ready segments
10. Common Excel pitfalls
11. Using Power Query for repeated cleanups
12. Keeping the best record when removing duplicates
13. Large files
14. Splitting cells with several addresses
15. Checking for hidden problems
16. Before and after a typical cleanup
17. Excel cleanup checklist
18. Frequently asked questions

Prepare your workbook

1. Save a copy of the original file and never edit it. Name it with the date, for example leads-original-2026-11.xlsx.
2. Put email addresses in column A with a header in row 1. Keep other columns (name, source, date) to the right.
3. Convert the range to a table (Insert, Table) so formulas extend automatically when you add rows.

The formulas below work in Excel for Microsoft 365, Excel 2021 and most older versions, except where noted.

Step 1: Fix formatting

Most "duplicates" and "invalid" addresses in Excel files are formatting problems: spaces, capitals, invisible characters copied from websites or PDFs. In column B:

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

• SUBSTITUTE(A2,CHAR(160)," ") replaces non-breaking spaces, common in copied web content.
• CLEAN removes non-printable characters.
• TRIM removes extra spaces.
• LOWER converts to lowercase.

Fill the formula down, then copy column B and use Paste Special, Values, so the cleaned addresses become plain text.

Step 2: Flag invalid formats

Excel has no built-in email validator, but this formula in column C catches the most common format errors:

=AND(LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1, ISERROR(FIND(" ",B2)), ISNUMBER(FIND(".",B2,FIND("@",B2))), RIGHT(B2,1)<>".")

It returns TRUE when the address contains exactly one @, no space, at least one dot after the @, and does not end with a dot. Filter on FALSE and review those rows. Correct obvious mistakes (a comma instead of a dot, a missing letter in ".com") and remove anything that is not an email address.

Step 3: Extract the domain

In column D:

=IFERROR(MID(B2,FIND("@",B2)+1,255),"")

Create a PivotTable (Insert, PivotTable) with the domain in Rows and Count of domain in Values, sorted from largest to smallest. Look at the bottom of the list: rare domains that resemble big providers are almost always typos.

Step 4: Flag common typos

In column E, flag known misspellings so you can review them:

=IF(ISNUMBER(MATCH(D2,{"gmial.com","gamil.com","gmal.com","gnail.com","hotmal.com","hotmial.com","yaho.com","yahooo.com","outlok.com","gmail.con","hotmail.con"},0)),"Check typo","")

Correct them with Find and Replace (Ctrl+H) only after checking each case. A full list of patterns is in common email typos.

Step 5: Find and remove duplicates

To see duplicates before removing them, use conditional formatting (Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values) on column B, or count them in column F:

=COUNTIF($B$2:$B$100000,B2)

Then remove them: select your table, choose Data, Remove Duplicates, and tick only the cleaned email column. Excel keeps the first occurrence. If you want to keep the most recent record instead, sort by date (newest first) before removing duplicates.

In Excel for Microsoft 365, you can also create a unique list with:

=UNIQUE(FILTER(B2:B100000,C2:C100000=TRUE))

Step 6: Remove addresses you should not email

• Addresses that unsubscribed or complained in your email platform (export your suppression list and remove matches with a lookup).
• Obvious test entries: test@, asdf@, your own team's test addresses.
• abuse@, postmaster@ and webmaster@ addresses.
• Contacts without a lawful basis for marketing.

To check against a suppression list in a sheet named "Suppress" (emails in column A):

=IF(COUNTIF(Suppress!$A:$A,B2)>0,"Suppressed","")

Step 7: Verify the mailboxes

At this point, your list is well formatted, deduplicated and free of obvious errors. It may still contain many addresses that no longer exist. Excel cannot check that. Export and verify:

1. Copy the cleaned, unique email column to a new sheet.
2. Save it as CSV UTF-8 (Comma delimited) to keep special characters intact.
3. Upload the file to your verification tool and download the results.
4. Open the results and bring the status back to your main sheet with XLOOKUP (or VLOOKUP in older versions).

=XLOOKUP(B2,Results!A:A,Results!B:B,"not verified")

Step 8: Build import-ready segments

Add a column with the verification date, so you can re-check the list in a few months.

Common Excel pitfalls

• Hidden spaces causing duplicates to survive: always normalize first.
• Auto-formatting turning addresses into hyperlinks: harmless, but paste as values to avoid surprises.
• Saving as regular CSV and losing accented characters in names: use CSV UTF-8.
• Sorting only one column and mixing up rows: always select the whole table before sorting.
• Editing the original file: keep the untouched copy.

Using Power Query for repeated cleanups

If you receive the same type of file every month (event exports, partner leads), Excel's Power Query saves a lot of time. You record the cleaning steps once and apply them to each new file:

1. Data, Get Data, From File, choose your CSV or workbook.
2. In the Power Query editor, select the email column, then Transform, Format, Trim, and Format, lowercase.
3. Use Remove Rows, Remove Duplicates on the email column.
4. Filter out empty rows and rows without "@".
5. Close and Load. Next month, replace the source file and click Refresh.

This gives you consistent results without retyping formulas, which reduces human error on large or frequent files.

Keeping the best record when removing duplicates

When the same address appears several times with different information (two names, two sources, two dates), decide which record to keep before removing duplicates:

Excel keeps the first occurrence it finds, so the sort order decides which record survives.

Large files

Excel handles about one million rows per sheet, but formulas become slow well before that. For lists above a few hundred thousand rows, clean with Power Query or upload the CSV directly to your verification tool, which removes duplicates automatically.

Splitting cells with several addresses

Some exports put several addresses in one cell, separated by commas or semicolons. Use Data, Text to Columns with the right delimiter, or, in Excel for Microsoft 365, =TEXTSPLIT(A2,{",",";"}). Then stack the resulting columns into one before deduplicating. Leaving multiple addresses in one cell makes the whole row invalid for import.

Checking for hidden problems

• Cells that look empty but contain spaces: use =LEN(A2) to find them.
• Addresses wrapped in angle brackets or quotes from copied email headers: remove them with Find and Replace.
• Names and addresses mixed, such as Anna Smith anna@example.com: extract the part containing @.
• Uppercase domains from some systems: already fixed by LOWER.

Each of these problems would otherwise appear as "invalid" in verification and cost you a credit for nothing.

Before and after a typical cleanup

On a typical event export, normalization alone often reveals duplicates that looked different only because of capitals or spaces, and the format check catches entries such as phone numbers or names typed in the email column. Fixing these in Excel takes minutes and means you spend verification credits only on real addresses.

Excel cleanup checklist

• Original file saved separately
• Spaces, invisible characters and capitals cleaned
• Format errors flagged and reviewed
• Domains extracted, typos reviewed
• Duplicates removed after normalization
• Suppressed and unwanted addresses removed
• Mailboxes verified and statuses added
• CSV UTF-8 export ready for import

Working in Google Sheets instead? See how to verify email addresses in Google Sheets. For the complete cleaning process beyond spreadsheets, see how to clean your email list.

Frequently asked questions

How do I remove duplicate emails in Excel?

Normalize the column with =LOWER(TRIM(A2)), paste the results as values, then use Data, Remove Duplicates on that column.

How can Excel find invalid email formats?

A formula checking for exactly one @, no spaces and a dot after the @ flags most format errors. Excel has no built-in email validator.

Can Excel fix typos like gmial.com?

Yes, with Find and Replace or SUBSTITUTE for the most common misspellings, after you review them. Do not auto-correct without checking.

Why do duplicates remain after Remove Duplicates?

Usually because of hidden spaces, uppercase letters or invisible characters. Normalize first, then deduplicate.

What should I do after cleaning in Excel?

Save as CSV (UTF-8), verify the addresses with a verification tool, and import only valid ones into your email platform.

BZ

Written by Badr Zemrani

Badr is an email marketing and SEO consultant with more than 15 years in digital marketing. He runs BZCOM, a digital agency in Casablanca, Morocco, where he plans, sends and troubleshoots email campaigns for businesses every week. SendCleanly is where he shares what actually works.

More about Badr · LinkedIn