B2B Email List Verification in 30 Minutes with Google Sheets

With a simple Google Sheets workflow and free tools, you can run a B2B email list verification on any purchased list in 30 minutes. You’ll run quick checks for syntax, share of non-work emails, MX record check, and job title and company fit. The result is a simple traffic-light scorecard that helps you decide to send, test, or replace the contacts.

TL;DR

• Green = Send: Send the list if syntax passes at 98% or higher, non-work addresses are 5% or less, all domains have mail servers, and the title and company match 90% or more on a 20-record sample.

• Yellow = Pilot: Send to a small number of the records and store the failed rows aside for replacement.

• Red = Reject: Definitely do not send these and store them aside for replacement.

How to Verify a B2B Email List in 4 Steps

Step 1: Fix Blanks & Validate Email Syntax (5 min)

Goal: Make sure every row has a valid, standardized email and passes a basic syntax check. Then calculate the Syntax "ok" percentage, aiming for 98% or higher, to spot obvious errors before running deeper tests.

Open a new Google Sheet, then do the following:

1. Create headers in row 1:

   1. Email in A1
   2. Normalized_email in B1
   3. Syntax_ok in C1

2. Paste your list of emails into column A under the email header

3. Make the normalized_email column in column B:

   1. Click B2 and paste the following formula, then press Enter:

      =LOWER(TRIM(A2))
      

   2. With B2 still selected, double-click the small blue square in the corner of the cell to fill the formula down the whole column.

This column is needed to create a cleaned version of each email. It removes extra spaces and converts all text to lowercase, which prevents false failures in later checks.

4. Check email syntax in column C:

   1. Click C2 and paste the following formula, then press Enter:

      =IF(REGEXMATCH(B2,"^\[A-Za-z0-9.\_%+\\\]+@\[A-Za-z0-9.\\\]+
      \\\[A-Za-z\]{2,}\$"),"ok","bad_syntax")
      

   2. Double-click the small blue square to fill it down.

5. Get your KPI, which is the percentage of "ok" in column C:

   1. Click any empty cell, like E2, and paste this:

      =COUNTIF(C2:C,"ok")/COUNTIF(B2:B,"\<\>")
      

   2. Format E2 as a percentage by selecting Format, then Number, then Percent.

Aim for a target of 100%. We set 100% as the target because anything lower suggests deeper issues such as poor parsing or outdated sources, which waste sends and cause preventable bounces. Scores at 100% show cleaner data, stronger deliverability, and less manual cleanup before you send.

Step 2: Non-Work Share of Emails (5 min)

Goal: Estimate how many emails are not real work inboxes. These include shared role addresses such as info@ or personal webmail like Gmail. They rarely belong in B2B outreach.

1. Add these headers in row 1 (continue in the same sheet):

   1. Local_part in D1
   2. Domain in E1
   3. Is_role in F1
   4. Is_free_webmail in G1
   5. Non_work_flag in H1

2. Make the Local_part column in column D. The local part is the text before the "@" in an email address:

   1. Click D2 and paste:

      =IFERROR(LEFT(B2, FIND("@",B2)-1),"")
      

   2. Double-click the small blue square to fill down.

3. Make the Domain column in column E. The domain is the text after the "@" in an email address:

   1. Click E2 and paste:

      =IFERROR(RIGHT(B2, LEN(B2)-FIND("@",B2)),"")
      

   2. Double-click the small blue square to fill down.

4. Check to see if the email is role-based in column F:

   1. Click F2 and paste:

      =IF(REGEXMATCH(D2,"^(info\|sales\|support\|help\|admin\|contact\
      |marketing\|billing\|careers\|jobs\|press\|media\|hr)\$"),
      "role","person")
      

   2. Double-click the small blue square to fill down.

      Role-based emails are usually a shared inbox that rarely get replies. As a result, you should keep the number as low as possible to improve engagement and deliverability.

5. Check to see if the email is free webmail, like @gmail or @yahoo:

   1. Click G2 and paste:

      =IF(REGEXMATCH(E2,"^(gmail\\com\|googlemail\\com\|yahoo\\com\|outlook
      \\com\|hotmail\\com\|aol\\com\|icloud\\com\|proton\\me)\$"),
      "free","corp")
      

   2. Double-click the small blue square to fill down.

      Free webmail emails are discouraged for B2B outreach because they usually belong to personal accounts, not business inboxes, which lowers reply and conversion rates.

6. Combine the role-based and free webmail columns into a single non-work flag:

   1. Click H2 and paste:

      =IF(OR(F2="role", G2="free"), "non_work", "work")
      

   2. Double-click the small blue square to fill down.

7. Compute your KPI, which is the percentage of non-work emails:

   1. Click any empty cell, like J2, and paste:

      =(COUNTIF(F2:F,"role")+COUNTIF(G2:G,"free"))/COUNTIF(B2:B,"\<\>")
      

   2. Format J2 as a percentage by selecting Format, then Number, then Percent.

Your target should be 5% or lower. Keeping non-work emails under this level helps you focus on real business inboxes instead of shared or consumer accounts that lower reply rates and distort deliverability. A smaller share is better, since fewer role or free addresses mean better targeting, clearer tracking, and less wasted send volume.

Step 3: MX Record Check and Checking if the Domain Accepts Mail (10 min)

Goal: Quickly confirm that each company domain can receive mail. A missing MX record is a strong warning sign in purchased B2B lists and often points to dead or fake domains.

1. Create a new tab for this check:

   1. Click the + at the bottom of the sheet and name it MX_check.

2. Add headers in row 1 in the MX_check tab:

   1. Domain in A1
   2. MX_result in B1
   3. Sample_flag in C1

3. Bring in the unique list of domains from Step 2 (column E on your main tab):

   1. Click A2 in MX_check and paste:

      =SORT(UNIQUE(FILTER(Main!E2:E, Main!E2:E\<\>"")),1,TRUE)
      

   2. Replace Main with the name of your first sheet if different.

4. Mark the first 100 to 200 rows as your sample:

   1. Click C2 and paste:

      =IF(ROW()-1\<=200,"sample","skip")
      

   2. Double-click the small blue square to fill down. If you want exactly 100, change 200 to 100.

5. Run an MX lookup for each sample domain:

   1. Pick one method:

      1. Web tool: Search for "MX lookup" using sites like MxToolbox or Google Admin Toolbox. Enter the domain, run the check, and copy the result.
      2. Using the Mac or Linux terminal: dig +short mx [sample domain]
      3. Using the Windows PowerShell or CMD: nslookup -type=mx [sample domain]

   2. If the lookup shows one or more MX records and no clear error, type "pass" in column B for that row.

   3. If the lookup shows no MX record or returns an error such as NXDOMAIN or timeout, type "fail" in column B for that row.

   

   

6. Compute your KPI, which is the percentage of MX checks that passed on the sampled rows:

   1. Click any empty cell, like E2, in MX_check and paste:

      =COUNTIFS(B:B,"pass",C:C,"sample")/COUNTIF(C:C,"sample")
      

   2. Format E2 as a percentage by selecting Format, then Number, then Percent.

Your target for the pass rate should be 100%. If a domain does not pass the MX check, then it will almost certainly bounce.

Step 4: 20-Record Human Spot-Check (10 min)

Goal: Confirm that a small, random sample matches your ICP. Check that the companies and decision-makers fit your target profile before you rely on the full list.

1. Add these headers on your main sheet (same tab as Steps 1 and 2):

   1. Random in I1
   2. Company_site_ok in J1
   3. Title_match_ok in K1

2. Create a random number for each row. This is so that we can pick 20 and random:

   1. Click I2 and paste:

      =RAND()
      

   2. Double-click the small blue square to fill down.

3. Sort by column I to get your sample:

   1. Select your entire table including headers.
   2. Select Data, then Sort range, then Advanced range sorting options, then check "Data has header row".
   3. Sort by column I with A to Z checked.
   4. The first 20 rows under the headers are your sample.

4. Check the company website in column J:

   1. For each of the 20 sampled rows, open the company website using the domain or company field.
   2. In J2 to J21, type "yes" if the website exists and fits your target industry or region. Type "no" if it does not exist or is outside your target.

5. Check the decision-maker title in column K:

   1. In K2 to K21, type "yes" if the job title is a decision-maker for your ICP, such as Director, VP, Head, Founder, Owner, or C-level. Type "no" if it is not.

6. Compute your KPI, which is the percentage of the titles and companies that match:

   1. Click any empty cell, like N2, and paste:

      =COUNTIFS(J2:J21,"yes",K2:K21,"yes")/20
      

   2. Format N2 as a percentage by selecting Format, then Number, then Percent.

Your target should be 90% or higher. A 90% pass rate or better on a random sample shows the vendor understood your ICP. Lower rates point to industry or title drift that will waste sends and time.

Traffic-Light Scorecard

Goal: Combine your four KPIs into one clear decision: GREEN, YELLOW, or RED.

1. Put your four KPI percentages as decimals into cells

   1. Put the Syntax OK value from step 1 into cell AA1
   2. Put the Non-work value from step 2 into cell AB1
   3. Put the MX checks value from step 3 into cell AC1
   4. Put the title and company match value from step 4 into cell AD1

2. Create the traffic-light cell

   1. Click any empty cell, like AE2, and paste:

      =IF(AND(AA2\>=0.98, AB2\<=0.05, AC2\>=1, AD2\>=0.90),"GREEN", 
      IF(AND(AA2\>=0.95, AB2\<=0.10, AC2\>=0.95,AD2\>=0.70), "YELLOW"
      ,"RED"))
      

   2. Now you’ll have the results! The list will have either a GREEN, YELLOW, or RED light.

What to Do with Each Result

• Green = (Send): Load the list into your email provider or CRM. Keep a suppression list for role and free accounts.

• Yellow = (Pilot): Send to only a small number of the records. Store the emails that failed syntax, MX, or non-work checks aside for replacement.

• Red = (Reject & Replace): Do not send these. Make sure to replace all failed records.

Conclusion

You’ve learned how to clean emails, check syntax, flag non-work addresses, test MX records, and confirm job title and company fit, then combine those checks into a single traffic-light scorecard. Now apply the workflow to your list and apply the following result: GREEN means load and send while keeping a suppression list, YELLOW means send only a small number as a record pilot and replace the failed rows, and RED means stop sending and replace the failed rows immediately. Save this page as a template and repeat the process for every new batch to keep quality and deliverability high.

FAQ

What’s a "non-work" email?

A non-work email is either a shared or role-based inbox such as info@ or sales@, or a personal address from a free webmail domain like Gmail or Yahoo. Keep these low in B2B lists since they rarely reach individual decision-makers.

Why treat "no MX" as a fail?

Treat "no MX" as a fail because most real businesses have MX records. While some domains without them can still receive mail, they often come from poor or fake data sources. It’s a simple and safe filter for purchased lists.

What if my sample fails the Title Match but the other metrics look good?

If your sample fails the Title Match but other metrics look good, run a pilot with about 5 to 10% of the list and track replies or booked meetings.