How to Verify Email Addresses in Google Sheets (Formulas and Verification)

Key takeaways

• Sheets formulas can normalize, deduplicate and check the format of addresses.
• ISEMAIL and REGEXMATCH flag obvious syntax errors in seconds.
• Formulas cannot tell whether a mailbox exists; that needs a verification tool.
• Export the cleaned column as CSV, verify it, then bring the statuses back into the sheet.
• Keep the original data untouched in a separate tab.

In this guide

1. Before you start
2. Step 1: Normalize the addresses
3. Step 2: Flag invalid formats
4. Step 3: Extract the domain
5. Step 4: Spot common typos
6. Step 5: Remove duplicates
7. Step 6: Verify the mailboxes
8. Step 7: Bring results back with a lookup
9. Tips for larger lists
10. Privacy and sharing
11. A ready-to-copy column layout
12. Using ARRAYFORMULA for speed
13. Sheets connected to Google Forms
14. Common mistakes in Sheets
15. Combining several sheets into one list
16. After verification: keeping the sheet useful
17. Google Sheets cleanup checklist
18. Frequently asked questions

Before you start

Google Sheets is perfect for a first cleanup of small and medium lists: event registrations, form exports, lead lists from partners. Before touching anything:

• Duplicate the original tab and name it "Original - do not edit". All work happens in a copy.
• Keep one column for the email, ideally column A, with a header row.
• Check consent and source: cleaning a list does not make it legal to email. Only verified addresses with permission go into marketing.

The steps below assume emails are in column A, starting at row 2.

Step 1: Normalize the addresses

In column B, create a clean version of each address:

=LOWER(TRIM(A2))

TRIM removes leading, trailing and repeated spaces; LOWER turns everything into lowercase so that Anna@Example.com and anna@example.com are recognized as the same address. Drag the formula down, then copy column B and paste it as values only (Edit, Paste special, Values only) so you can work on plain text.

Google Sheets also has a built-in option: select the column, then Data, Data cleanup, Trim whitespace.

Step 2: Flag invalid formats

Sheets has a dedicated function:

=ISEMAIL(B2)

It returns TRUE when the value looks like an email address. For a check you control, use a regular expression instead:

=REGEXMATCH(B2, "^[^\s@]+@[^\s@]+\.[^\s@]+$")

Filter column C on FALSE to see every malformed entry. Fix obvious errors by hand (for example a comma instead of a dot), and delete entries that are not email addresses at all. The pattern is explained in email validation regex.

Step 3: Extract the domain

The domain tells you a lot: typos, disposable services, business vs personal addresses. In column D:

=REGEXEXTRACT(B2, "@(.+)$")

Then create a quick summary of domains with a pivot table (Insert, Pivot table, rows: domain, values: COUNTA of domain). Sort by count. The top of the list shows your main providers; the long tail often reveals typos such as gmial.com or hotmal.com.

Step 4: Spot common typos

Flag the most frequent misspellings with a formula in column E:

=IF(REGEXMATCH(D2, "^(gmial|gamil|gmal|gnail)\.com$|^(hotmal|hotmial|hotmai)\.com$|^(yaho|yahooo)\.com$|^outlok\.com$|\.con$|\.cmo$"), "Check typo", "")

Review each flagged row before correcting. Some "typos" are real domains, and a correction should never be applied blindly. My guide on common email typos lists more patterns.

Step 5: Remove duplicates

To see duplicates first, add a count in column F:

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

Any value above 1 is a duplicate. To remove them, select the data range, then Data, Data cleanup, Remove duplicates, and choose column B. Alternatively, create a clean list in a new tab with:

=UNIQUE(FILTER(Sheet1!B2:B, Sheet1!C2:C=TRUE))

This returns each valid-format address once.

Step 6: Verify the mailboxes

Everything so far checks format, typos and duplicates. Sheets cannot tell you whether anna@example.com still exists. For that, you need a verification service:

1. Export only the clean email column: File, Download, Comma-separated values (.csv), from a tab containing just that column.
2. Upload the CSV to your verification tool.
3. Download the results, which add a status for each address (valid, invalid, disposable, role, catch-all, unknown).
4. Import the results back into a new tab (File, Import), then bring the status next to your data with a lookup.

Step 7: Bring results back with a lookup

If the results are in a tab called "Results" with the email in column A and the status in column B, add this in your working tab:

=IFERROR(VLOOKUP(B2, Results!A:B, 2, FALSE), "not verified")

You now have every contact with its verification status. Filter to build your segments:

Tips for larger lists

• Formulas slow down with tens of thousands of rows. Paste results as values once calculated.
• Work by batch if needed, or move to the CSV directly in your verification tool.
• Add a "verified on" date column, so you know when to re-check the list.

Privacy and sharing

Email lists are personal data. Restrict sharing of the sheet to the people who need it, avoid public links, and delete exports from your computer once imported. When you upload to a verification service, send only the email column, not names, phone numbers or notes. See is email verification GDPR compliant? for the data protection side.

A ready-to-copy column layout

With this layout, a single filter on columns C, E and F gives you every segment you need.

Using ARRAYFORMULA for speed

Instead of dragging formulas down thousands of rows, you can use one formula per column. For example, in B2:

=ARRAYFORMULA(IF(A2:A="", "", LOWER(TRIM(A2:A))))

This fills the whole column automatically and updates when new rows are added, which is convenient for sheets connected to a form.

Sheets connected to Google Forms

If your sheet collects answers from a form, new addresses arrive continuously, unverified. Two options keep them clean: verify the new rows in bulk every week, or replace the form with one that supports real-time verification so bad addresses never reach the sheet. The second option saves time and avoids sending your welcome email to typos. See real-time email verification.

Common mistakes in Sheets

• Removing duplicates before normalizing, so Anna@Mail.com and anna@mail.com both stay.
• Sorting one column only and mixing up rows.
• Sharing the sheet with "anyone with the link".
• Uploading the whole sheet, with names and phone numbers, to a verification tool when only emails are needed.

Combining several sheets into one list

Lists often come in several files: one per event, one per salesperson, one per month. To merge them in Google Sheets:

1. Import each file into its own tab.
2. Create a "Combined" tab with a formula that stacks the email columns, for example ={Event1!A2:A; Event2!A2:A; Event3!A2:A}.
3. Normalize and deduplicate the combined column as described above.
4. Add a column for the source, so you remember where each address came from.

Merging before verification saves credits, because duplicates across files are removed first.

After verification: keeping the sheet useful

Once your contacts are imported into an email platform, the sheet becomes a historical record. Keep the version with verification statuses and dates in a secure place, delete working copies, and avoid maintaining two master lists in parallel. The email platform should become the single source of truth.

Google Sheets cleanup checklist

• Original tab kept untouched
• Addresses trimmed and lowercased
• Invalid formats flagged with ISEMAIL or REGEXMATCH
• Domain extracted and typos reviewed
• Duplicates removed
• Mailboxes verified with a verification tool
• Statuses brought back with a lookup and segments built
• Sheet sharing restricted

Prefer Excel? The same method with Excel formulas is in how to clean an email list in Excel. Importing into a CRM next? Read verify emails before importing them into your CRM.

Frequently asked questions

Does Google Sheets have an email validation function?

Yes. ISEMAIL returns TRUE when a value looks like an email address. It only checks format.

How do I remove duplicate emails in Google Sheets?

Normalize addresses first (trim and lowercase), then use Data, Data cleanup, Remove duplicates, or the UNIQUE function.

Can I verify mailboxes directly from Google Sheets?

Some verification services offer add-ons or API connections. The simplest method is to export a CSV, verify it, and import the results back.

Is it safe to upload my sheet to a verification tool?

Use a reputable provider, upload only the email column if possible, and check how long the provider keeps your files.

How many addresses can Google Sheets handle?

Sheets supports large ranges, but formulas slow down on very big lists. For lists above tens of thousands of rows, work on the CSV directly with a verification tool.

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