Practical

How to Clean an Email List in Excel or Google Sheets

A black-and-white cutout of a spreadsheet grid printed on paper with a red pen on a flat mint-green background

You have a CSV of contacts, a second CSV of addresses that bounced or unsubscribed, and a spreadsheet. That is enough to clean a list properly, provided you do it in the right order and never, at any point, sort a single column by itself.

This guide gives you the exact formulas for Excel and Google Sheets: trim the stray spaces, lowercase everything, flag duplicates, catch addresses with no @ sign, and remove every row that appears on your suppression list. It works well up to a few thousand rows. Past that, the last section explains why you will want something else.

Key takeaways

  • Normalize first: =TRIM(LOWER(A2)) turns [email protected] into [email protected] so matches stop failing silently.
  • Flag, do not delete. Add a column that says keep or remove, filter, and copy the keepers out.
  • A COUNTIF against the suppression column does the removal; a FIND("@") catches the obviously broken; REGEXMATCH in Sheets catches the rest.
  • Keep every column together. Convert the range to a table or select the whole sheet before every sort.

Set up the sheet before touching a formula

Five minutes here prevents the one mistake you cannot undo.

  1. Work on a copy. Save the original CSV with today's date in the name and open the copy.
  2. Confirm the header row. Row 1 should be column names. If the export put a title or a blank line above it, delete those rows so the data starts in row 2.
  3. Find the email column. This guide assumes it is column A. If it is not, either move it or substitute your column letter in every formula below.
  4. Paste the suppression list. Open your bounce and unsubscribe exports, copy the email column from each, and paste them one under another into column H of the same sheet, starting at H2. Leave columns B through G free for the formulas below. If your list already uses those columns, insert blank columns after A.
  5. Convert the data to a table (Excel: select the range, Insert, Table) or get in the habit of pressing the select-all corner before any sort (Sheets). Either way, rows must move as units.

Step 1: trim and lowercase

Email addresses are not case-sensitive in practice, but spreadsheet matching is, and exports are full of trailing spaces. Build a clean key column.

In B2:

=TRIM(LOWER(A2))

Fill down to the last row. Do the same for the suppression column: in I2, =TRIM(LOWER(H2)), filled down to the last suppressed address.

Some exports carry a non-breaking space that TRIM ignores. If matches fail on addresses that look identical, use this version instead:

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

Every later formula points at column B, not column A. Column A is your evidence of what the data looked like before you touched it.

The one unrecoverable mistake. Sorting column B alone, or column E alone, moves the email addresses while every other column stays put. Every name is now beside the wrong address. A CSV has no undo after save. Convert to a table, or select everything before every sort, every time.

Step 2: flag duplicates

Duplicates inflate your bill and double-send. The formula below marks the second and later copies of each address and leaves the first copy unflagged, so one row per person survives.

In C2:

=IF(COUNTIF($B$2:B2,B2)>1,"dup","")

The mixed reference $B$2:B2 is deliberate: it counts only the rows above and including the current one, which is what makes the first occurrence come up clean. If you would rather keep the row with the most filled columns than the first one, sort by a completeness score first; the dedupe guide shows how.

To see how many duplicates you have, put =COUNTIF(C:C,"dup") somewhere out of the way. On a list that has been through a couple of imports, 1% to 3% is typical.

  1. Prepare a copy with the suppression list beside it

    Save a dated copy, confirm the header is in row 1 with emails in column A, and paste every suppressed address into column H.

  2. Normalize both columns

    In B2 enter =TRIM(LOWER(A2)) and in I2 enter =TRIM(LOWER(H2)); fill both down.

  3. Flag duplicates and bad formats

    In C2 use =IF(COUNTIF($B$2:B2,B2)>1,"dup","") and in D2 use =IF(ISNUMBER(FIND("@",B2)),"","no @"), or REGEXMATCH in Sheets.

  4. Flag suppressed rows

    In E2 use =IF(COUNTIF($I$2:$I$5000,B2)>0,"remove","keep") and fill down; check the count against your platform's bounce report.

  5. Filter and copy the keepers

    Filter C and D to blank and E to keep, copy the visible rows of your original columns to a new sheet, and confirm the row count.

  6. Save and re-import

    Save as CSV UTF-8, import with the replace or update option, and add the suppression addresses to your platform's do-not-mail list.

Step 3: flag addresses that cannot be valid

This is not verification. It catches the addresses that fail on shape alone: a phone number in the email field, a missing @ sign, a space in the middle.

Simplest check, in D2:

=IF(ISNUMBER(FIND("@",B2)),"","no @")

Slightly stricter, still Excel-friendly, same cell:

=IF(AND(ISNUMBER(FIND("@",B2)),ISNUMBER(FIND(".",B2,FIND("@",B2)+1)),LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1,ISERROR(FIND(" ",B2))),"","bad format")

That requires exactly one @, a dot somewhere after it, and no spaces. In Google Sheets a regular expression does the same in one line:

=IF(REGEXMATCH(B2,"^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$"),"","bad format")

Recent Excel 365 builds have a REGEXTEST function that accepts the same pattern. The invalid address guide extends these checks to typo domains like gamil.com and role accounts like info@.

Step 4: remove the suppression list

Now the main event. For each row, check whether its normalized address appears anywhere in the normalized suppression column.

In E2:

=IF(COUNTIF($I$2:$I$5000,B2)>0,"remove","keep")

Change 5000 to a row number past the end of your suppression list. MATCH does the same job and is faster on long ranges:

=IF(ISNUMBER(MATCH(B2,$I$2:$I$5000,0)),"remove","keep")

Fill down. Then put =COUNTIF(E:E,"remove") in a spare cell and compare it to your expectations. If your platform reported 640 bounces and the formula flags 3, something is wrong, usually a suppression column that was not lowercased or a stray header pasted in at H2.

This is a suppression pass in the exact sense: you are removing addresses already known to be bad, not guessing. The suppression list guide explains why that is the right first step and what belongs in column H.

Step 5: filter, copy the keepers, save as CSV

Do not delete the flagged rows. Filter them out and copy what remains.

  1. Turn on filters for the header row.
  2. Filter column C to blanks, column D to blanks, and column E to keep.
  3. Select from A1 to the last visible row and column of your original data, not the helper columns.
  4. Copy. In Excel, use Go To Special, Visible cells only, before copying; in Sheets, copying a filtered range copies only the visible rows.
  5. Paste into a new blank sheet. Confirm the row count equals your original minus the removals.
  6. Delete the helper columns if you pasted them. Save as CSV UTF-8.

Re-import to your platform with the "update existing" or replace option so the removed addresses do not come back. Then upload the suppression addresses from column H to your platform's do-not-mail list, so a form or integration cannot re-add them.

Where the spreadsheet stops being worth it

Everything above is reliable to roughly 2,000 rows and tolerable to 10,000. Beyond that, three things happen.

  • Speed. COUNTIF across 50,000 rows against 10,000 suppressed addresses is 500 million comparisons. Excel stalls; Sheets reports that it cannot finish.
  • Multiple files. Bounces from one platform, unsubscribes from another, a complaint export from a third. Each needs pasting, lowercasing, and de-duplicating before the match. The merge guide covers it, and it adds 20 minutes a month.
  • Repetition. This is a monthly job. The fourth time through, the helper columns feel less like a method and more like a chore.

Email List Refresh does this exact procedure as a service: upload the full CSV and every suppression file at once, and it returns the original with every column intact and only the suppressed rows removed, case-insensitive, de-duplicated across files, no verification and no guessing. It is $19.99 a month for unlimited lists, and a free account lets you look before paying. Below a couple of thousand rows the spreadsheet is the right tool. Above it, your time is the expensive part.

Free estimate

See what this costs your list

Pick your list size. We will show you the math, then send you a one-page cleanup plan for your platform.

Your estimatePick a list size and platform to see the numbers.
—addresses likely dead this year
—paid per year to store them
—contacts that can still be reached

No spam, no list rental. One email with your plan, then nothing unless you ask.

FAQ

Questions about clean an email list in excel or google sheets

How do I clean an email list in Excel?

Make a copy, put the emails in column A and your suppression addresses in column H. Normalize both with =TRIM(LOWER(A2)), flag duplicates with =IF(COUNTIF($B$2:B2,B2)>1,"dup",""), flag bad formats with a FIND("@") check, and flag suppressed rows with =IF(COUNTIF($I$2:$I$5000,B2)>0,"remove","keep"). Filter to the clean rows, copy them to a new sheet, and save as CSV.

How do I remove duplicate emails in Excel without losing data?

Build a lowercase, trimmed key column and use =IF(COUNTIF($B$2:B2,B2)>1,"dup","") to mark the second and later copies, then filter them out rather than using Remove Duplicates, which gives you no control over which row survives. To keep the fullest row, sort by a completeness score such as =COUNTA(C2:J2) descending before applying the flag.

How do I remove a suppression list from my contacts in Google Sheets?

Paste the suppressed addresses into a spare column, lowercase and trim both that column and your email column, then in a new column use =IF(COUNTIF($I$2:$I$5000,B2)>0,"remove","keep"). Filter to "keep", copy the visible rows to a new sheet, and download as CSV. Rows must stay together, so select the whole sheet before any sort.

Can Excel check if an email address is valid?

Excel can check the shape of an address: exactly one @, a dot after it, no spaces. It cannot tell you whether the mailbox exists; that requires contacting the mail server. Format checks catch typed-in phone numbers and obvious mistakes. Known bounces are better removed from your platform's own bounce export.

Why does my COUNTIF not find emails that are clearly on both lists?

Almost always case or whitespace. One copy is [email protected] and the other [email protected], or one has a trailing space or a non-breaking space from the export. Point the formula at lowercased, trimmed helper columns on both sides, and use the SUBSTITUTE(…,CHAR(160),"") variant if plain TRIM is not enough.

Clean your list before the next send.

Upload your contacts and every suppression file you have. Download a clean CSV with every column intact. Seven minutes, start to finish.

Envelopes drop into a funnel: good addresses stack up, bad ones are thrown out