How to Clean an Email List in Google Sheets

Many small lists live in Google Sheets: a sign-up form that writes to a sheet, a shared contact list, a conference attendee file. A sheet can sort and de-duplicate, but it cannot tell you whether a mailbox exists. Here is a safe way to clean one, from start to finish.

Step 1: Make a copy and tidy the column

Duplicate the sheet first so you can always go back. Put the addresses in one column with a header such as Email. In a helper column use =LOWER(TRIM(A2)), fill it down, then copy the helper column and paste it back as values only. Spaces and capital letters make valid addresses look broken and hide duplicates.

Step 2: Remove duplicates

Select the column and use Data → Data cleanup → Remove duplicates. Check the box that says your data has a header row. If you have other columns that belong to each address, select the whole table so rows stay together.

Step 3: Export and verify

Use File → Download → Comma-separated values (.csv) or Microsoft Excel (.xlsx). Upload the file on the bulk verification page. Mailcut finds the column that holds the addresses, checks each one and returns a report with a result and a reason for every row.

Step 4: Bring the results back

Open the report and copy the result column. Paste it next to your addresses in the original sheet, matching on the email address. A simple =VLOOKUP(A2, report!A:C, 3, FALSE) works when the report is imported as a second tab. Now filter your sheet by result:

• Valid: keep and use.
• Invalid: delete or move to an archive tab.
• Accept-all and risky: keep in a separate tab and send to them in small batches first.
• Unknown: check again later, because the receiving server may have been busy.

Keep a form from refilling the problem

If a Google Form feeds the sheet, add a short question that asks people to type their address twice, or use the built-in response validation for email format. It will not prove that a mailbox exists, but it stops many typos before they enter the sheet.

How many credits will this use?

Each verified address uses one credit, and credits you buy are valid for 12 months, so a small sheet costs a small amount and nothing renews on its own. Remove duplicates before you upload, so you do not spend credits on the same address twice. You can try the whole process on a small sample with the free credits of a new account. See the pricing page for the current packs.

Cleaning once before every big send is usually enough for a list that changes slowly. For a list that grows every day, repeat the clean when you reach a send that matters.

Common mistakes to avoid

• Verifying the file with formulas still in it. Paste values only before you export, or the export may carry formulas or broken references instead of addresses.
• Deleting rows before you save a copy. Keep the original tab, so you can always check what was removed and why.
• Mixing several addresses in one cell. Split cells such as "[email protected]; [email protected]" into separate rows first, otherwise a good address is judged together with a bad one.
• Cleaning only once. A sheet fed by a form keeps collecting new mistakes, so repeat the check before important sends.

Questions people ask

Can I verify directly from the sheet? Not without an add-on. The simple route is download, upload, bring the result back, which takes a few minutes for most lists.

How do I keep the original order? Add a column with row numbers before you export. After the results are back, sort by that column to return to the original order.

What about addresses with a plus sign? Addresses such as [email protected] are valid and reach the same inbox as [email protected]. See the post on plus addressing before you treat them as duplicates.