How to Verify a B2B Email List in Excel

Last updated on 10/28/2025 · 14 min read

This guide shows how to verify a B2B email list in Excel using only built-in formulas and Power Query. You will clean and normalize addresses, flag non-work emails, sample MX records, and add a quick human QA step. The process produces a 100-point quality score so you can decide at a glance whether to send, pilot, or replace a list.

TL;DR

Normalize emails, remove duplicates, and run a syntax check in Excel.
Split the local part and domain, flag role-based and free webmail addresses, and keep the non-work share at 5 percent or less.
Export a unique domain list, sample MX records with one PowerShell command, and merge the results back with Power Query to create a pass or fail column.
Audit 20 random rows to confirm the company site exists and the title fits a decision maker.
Combine all checks into a 100-point score with a traffic light icon to decide whether to Send, Pilot, or Replace.

What you’ll need

Excel for Microsoft 365
Windows for the quick MX check with nslookup or Resolve-DnsName

Module A: Normalize & De-Duplicate Emails

This module prepares your email list for accurate checks later. Begin by converting every address to lowercase so formatting stays consistent. Remove any extra spaces at the start, end, or within each address. Then delete exact duplicates to keep only one copy of each record.

Put your raw emails in Column A.
1. The header cell A1 should say “Email”.
2. Your first email should be in A2.
Create a cleaned version in Column B.
1. Click B1 and type “Normalized_email”.
2. Click B2 and paste this formula:
```
=LOWER(TRIM(A2))
```

This turns “John.Doe@Company.COM” to “john.doe@company.com” and removes extra spaces that might block proper matching.

Copy the formula down the whole column by doing the following:
1. Place your mouse on the small square at the bottom-right corner of cell B2, called the fill handle. When the pointer changes to a black plus sign, double-click. Excel will copy the formula down automatically to the last row that contains data in Column A.
Remove exact duplicates in Column B.
1. Click anywhere inside Column B.
2. Open the “Data” tab, then select “Remove Duplicates”.
3. In the dialog box, select only “Normalized_email” and clear all other boxes. Then click “OK”.

Column B now shows clean, lowercase emails with no spaces at the start or end. After running “Remove Duplicates”, each entry in column B appears only once. You can keep Column A with the original addresses for reference.

Module B: Split Local Part and Domain for Better Filtering

The goal of this module is to split each email into two parts: the text before @ (the user) and the text after @ (the domain). You will use these fields later to spot role-based addresses and to check domains.

Add the necessary headers:
1. Click on C1, then type “Local_part”.
2. Click on D1, then type “Domain”.
Extract the text before the “@” symbol to get the username:
1. Click on C2 and paste:
```
=IFERROR(TEXTBEFORE(B2,"@"),"")
```
Extract the text after “@” symbol to get the domain:
1. Click on D2 and paste:
```
=IFERROR(TEXTAFTER(B2,"@"),"")
```
Copy both formulas down the list:
1. Select C2 and double-click the square at its bottom-right corner. Repeat for D2.

In C2 you should see something like “john.doe” and in D2 you should see something like “company.com”.

Module C: Validate Email Syntax

This module catches clearly incorrect emails before running deeper checks. Each row will be marked as “ok” or “bad_syntax”, and you’ll calculate the overall pass rate at the end.

Add the following header:
1. Click on E1, then type “Syntax_ok”.

Check each email by doing the following:

Click on E2 and paste:

=IFERROR(IF(AND(ISNUMBER(FIND("@",B2)),
LEN(TEXTBEFORE(B2,"@"))\>0,ISNUMBER(FIND(".",D2)),
LEN(TEXTAFTER(D2,".",-1))\>=2),"ok","bad_syntax"),
"bad_syntax")

This step checks that each email contains an “@,” has text before it, includes a dot in the domain, and ends with at least two characters after the dot.

Copy the above formula down the list:
1. Double-click the tiny square at the bottom-right of E2.
Calculate your pass rate:
1. Pick an empty cell and paste:
```
=COUNTIF(E2:E,"ok")/COUNTIF(B2:B,"\<\>")
```
2. Go to the “Home” tab and click the % button to format the value as a percentage.

Aim for a pass rate of 100%. Anything lower and emails will definitely bounce.

Module D: Flag Role-Based and Free-Webmail Addresses

The goal of this module is to flag emails that are poor B2B targets. These include role-based usernames such as info@ or sales@, and free-webmail domains such as gmail.com or yahoo.com. We’ll then combine both checks into a single Non_work_flag field and measure the share of these rows.

Create the role prefixes column in order to be used to check role-based usernames:
1. Pick some empty cells, like L1:L13.
2. In L1:L13, enter:
  1. info
  2. sales
  3. support
  4. help
  5. admin
  6. contact
  7. marketing
  8. billing
  9. careers
  10. jobs
  11. press
  12. media
  13. hr
3. Select cells L1:L13, click the “Name Box” to the left of the formula bar, type “RolePrefixes”, then press Enter.
Create the free-webmail column in order to be used to check for free domains:
1. In M1:M8, enter:
  1. gmail.com
  2. optonline.net
  3. yahoo.com
  4. outlook.com
  5. hotmail.com
  6. aol.com
  7. icloud.com
  8. proton.me
2. Select cells M1:M8, click the “Name Box”, type “FreeWebmail", then press Enter.
Create the Is_role column to check if the usernames are role-based.
1. Click F1, then type “Is_role”.
2. Click F2 and paste this:
```
=IF(SUMPRODUCT(--(LEFT(C2,LEN(RolePrefixes))=RolePrefixes))
\>0,"role","person")
```
3. Copy it down the entire column.
Create Is_free_webmail column to check if the domains are free-webmail.
1. Click G1, then type “Is_free_webmail”.
2. Click G2 and paste this:
```
=IF(COUNTIF(FreeWebmail,D2)\>0,"free","corp")
```
3. Copy it down the entire column.
Create the Non_work_flag column to combine the results of columns F and G.
1. Click H1, then type “Non_work_flag”.
2. Click H2 and paste this:
```
=IF(OR(F2="role",G2="free"),"non_work","work")
```
3. Copy it down the entire column.
Pick an empty cell, like J2, and paste:
```
(COUNTIF(F:F,"role")+COUNTIF(G:G,"free"))/COUNTIF(B:B,"\<\>")
```
1. Go to the “Home” tab and click the % button to format the value as a percentage.

Target a non-work rate of 5% or less. Role-based and free-mail addresses often have lower reply rates and can skew deliverability results.

Before Module E: Build the “LeadsMain” Table for Power Query Joins

Let’s create a structured Excel table that contains your working rows from Modules A through D and includes only these columns:

A: Email
B: Normalized_email
C: Local_part
D: Domain
E: Syntax_ok
F: Is_role
G: Is_free_webmail
H: Non_work_flag

Keep KPI cells, such as the % in J2, and the lookup lists in L1:L13 and M1:M8 for RolePrefixes and FreeWebmail outside the table.

Create the table by following these steps:

Select cells from A1 through column H down to the last data row.
Press Ctrl+T, then check “My table has headers.”
With the table selected, go to “Table Design”, then “Table Name” and rename it LeadsMain.

You will use LeadsMain in Module E to merge with the imported MX table on the Domain column using a Left Outer join.

Module E: Check MX Records at Scale and Merge in Power Query

The goal in this module is to test if each company domain in your list can receive mail. We will check all unique domains, mark each as pass if it has MX or fail if it has none, and add the result to your LeadsMain table.

Create an empty sheet and choose a name, like Sheet2 for example.
In A1, type “Domain”.
In A2, paste this formula and press enter:
```
=UNIQUE(FILTER(Sheet1!D:D,Sheet1!D:D\<\>""))
```
1. Replace “Sheet1!D:D” if your Domain column is elsewhere.
2. You should now see a clean, single-column list of unique domains.
Export this list to a text file:
1. Select the filled cells in column A, starting from A2. Press Ctrl + C to copy.
2. Open Notepad, then press Ctrl + V to paste one domain per line.
3. Save the file as domains.txt on your Desktop or in your project folder.
Open PowerShell by pressing the Windows key, typing PowerShell, and pressing Enter.
Change to the folder where you saved domains.txt, for example:
```
cd \$HOME\Desktop
```

Run the following command to create mx_results.csv with each domain marked as pass or fail:

Get-Content .\domains.txt \| % { try { Resolve-DnsName -Name
\$\_ -Type MX -ErrorAction Stop \| Out-Null; "\$\_,pass" } catch
{ "\$\_,fail" } } \| Set-Content .\mx_results.csv

“Pass” means the domain returned at least one MX record, showing it accepts emails. “Fail” means no MX record was found, which usually means the domain is inactive or misconfigured.

Back in Excel, open the “Data" tab, then go to “Get & Transform Data” and choose “From Text/CSV”.
Select mx_results.csv, then click Load to import the data into Excel.
Excel will create a new table, usually named mx_results.
1. Click anywhere in the table, go to “Table Design” and rename it to “MX”.
Click anywhere in your LeadsMain table from Module D, then go to the “Data” tab, choose “Get Data”, and select “Launch Power Query Editor”.
In Power Query, open the “Home" tab and choose “Merge Queries”, then select “Merge Queries” again from the menu.
In the merge dialog:
1. The top table is your main table, and click the Domain column to select it as the key.
2. The bottom table is MX, and click its domain column.
3. The join kind is Left Outer, all from first and matching from second.
4. Click OK.
You will see a new column with a small table icon. Click the expand button, select only the result column which may be named “Column2” or result depending on the import, uncheck “Use original column name as prefix”, and click OK.
Rename the new column to “MX_result”. The values will be “pass” or “fail”.
Click the “Home” tab, then choose “Close & Load” to push the merged results back into Excel.
Pick a KPI cell near your other metrics and paste the following formula:
```
=COUNTIF(\[MX_result\],"pass") / COUNTIF(D:D,"\<\>")
```
Format this cell as a %.

Aim for a 100% pass rate on B2B lists. If it is lower, investigate the source because the emails will certainly bounce.

Module F: 20-Row Human Spot Check

The goal of this module is to do a quick human spot check on 20 random contacts to catch issues formulas miss, such as wrong company, wrong titles, or dead sites. Aim for 90% or higher passing.

Click on I1, then type “Random”
Click on I2 and paste:
```
=RAND()
```
Copy it down to the last row.
Select the filled cells in column I starting from I2 down, press Ctrl+C, then go to Home, choose “Paste”, and select “Paste Values” shown as a clipboard with 123. This makes the random numbers stay fixed.
Click any cell in your data.
Go to “Data” then “Sort”.
Sort by “Random”, then “Smallest to Largest”, then click “OK”.
Your sample is now the top 20 rows, from row 2 through row 21.
Click on Q1, then type “Company_site_ok”.
1. This means checking whether the company website exists and matches your target industry and region. Ask three quick questions. Does the domain resolve? Is it the same company the email claims? Does the site match your ideal customer profile?
Click on R1, then type “Title_match_ok”.
1. This means checking if the person is a decision maker for your ideal customer profile. Use this quick guide and adjust it for your business: Yes for Owner, Founder, CEO, President, VP, or Director or Head of Sales, Marketing, Operations, IT, HR, Finance, or Procurement. No for interns, assistants, junior or associate roles, generic Team titles, or unrelated functions.
Have a human reviewer mark cells Q2 through Q21 and R2 through R21 as “yes” or “no” for each of the 20 rows.

Pick a KPI cell, like N2, and paste:

=COUNTIFS(Q2:Q21,"yes",R2:R21,"yes")/20

Format the cell as a %.

You should aim for 90% or higher. If you are below that, tighten your filters or replace your data.

Module G: 100-Point Quality Score with Traffic-Light Icons

The goal now will be to combine your checks into one score from 0 to 100 so you can decide whether to Send, Pilot, or Replace them.

Pick an empty area, like N1:O6.
In N1:N4, type these labels:
1. Syntax_OK_%
2. Non_work_%
3. MX_pass_%
4. Title+Company_match_%
In O1:O4, link to the actual cells where you calculated those metrics earlier.
1. Example: if your Syntax OK % is in Z2, then in O1 type “=Z2”, etc.
Name the four cells so formulas are easy to read:
1. Click cell O1, then in the “Name Box” to the left of the formula bar, type “Syntax_OK_Pct” and press Enter.
2. Click cell O2 and name it “Non_work_Pct” in the same way as above.
3. Click cell O3 and name it “MX_pass_Pct”.
4. Click cell O4 and name it “QA_pass_Pct”.
In N6:N9, type the labels:
1. SCORE_SYNTAX (40%)
2. SCORE_NONWORK (20%)
3. SCORE_MX (25%)
4. SCORE_QA (15%)
In O6:O9, enter these formulas:
1. In O6, enter the following:
```
=40 \* Syntax_OK_Pct
```
2. In O7, enter the following:
```
=20 \* (1 - Non_work_Pct)
```
3. In O8, enter the following:
```
=25 \* MX_pass_Pct
```
4. In O9, enter the following:
```
=15 \* QA_pass_Pct
```

The weights in step 6 match typical B2B impact. Syntax cleanliness matters most, followed by MX deliverability, then non-work dilution, and finally a quick human spot check. You can adjust them later if needed.

In N11, type “Total_Score”.
In O11, paste:
```
=ROUND(SUM(O6:O9),0)
```
1. This gives a whole-number score from 0 to 100.
Click O11.
Go to the “Home” tab, choose “Conditional Formatting”, then select “Icon Sets” and pick 3 Traffic Lights.
With O11 still selected:
1. Open Conditional Formatting, choose Manage Rules, and select Edit Rule.
2. Change the Type setting for each threshold to Number instead of Percent.
3. Set:
  1. Green when the value is greater than or equal to 85.
  2. Yellow when the value is greater than or equal to 70.
  3. Red otherwise.
4. Click “OK”, then click “OK” again.

How to Use the Score

Green: This score means the list is ready to use in your sender or CRM. You can proceed with your normal warm-up process and apply list-level throttling to control send volume.

Yellow: This range suggests partial confidence in the data. Send a small test campaign first, then replace or refresh any rows that fail checks such as invalid syntax, missing MX records, too many non-work addresses, or QA results marked no.

Red: This score indicates that the list quality is too low to use safely. Do not send to it, and instead replace the list.

FAQs

Does this require the newest Excel?

You need Microsoft 365 or Excel 2021 or later to use functions like TEXTBEFORE, TEXTAFTER, TEXTSPLIT, UNIQUE, and FILTER. Older versions can handle the process with Text to Columns, LEFT, RIGHT, FIND, and Remove Duplicates, but the work will be more manual.

Can I automate the 20-row QA pick?

Yes. Keep the RAND helper column, sort in ascending order, and take the first 20 rows after recalculating. This method is quick, unbiased, and easy to explain.

Ready to reach fresh, human-verified leads today?

Start for Free