Guides
Last Updated Aug 03, 2023

Email Validation Excel

Elizabeth (Lizzie) Shipton

Table of Contents:

Get your free
API
key now
4.8 from 1,863 votes
See why the best developers build on Abstract
START FOR FREE
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
No credit card required
Get your free
Email Verification API
key now
4.8 from 1,863 votes
See why the best developers build on Abstract
START FOR FREE
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
No credit card required

From temporary accounts, to spam, to typos, invalid domains, and expired MX records, there are many ways in which the precious email addresses in your mailing list might be invalid. Fortunately, finding and removing those addresses from your list is easy.

In this tutorial, we’ll use filters, and data validation to validate email addresses in an Excel spreadsheet. We’ll also look at an alternative method of validating emails using the AbstractAPI Free Email Validation Endpoint.

Working in Google Sheets? Check out how to validate email addresses in Google Sheets.

What Is a Valid Email Address?

You may be surprised to learn that there are many valid formats for an email address, and this makes validating an email using methods like regular expressions and syntax rules difficult.

For example, some domains like Gmail allow users to include the ‘+’ sign in an address, while other systems don’t allow this character. Top-level domains are not always three letters (.com, .org, or .net.) Hyphens are sometimes allowed. Emails are case insensitive. They can contain more than one period in a row.

For these reasons, writing a regular expression or syntax rule that can effectively capture every correct email address format is impossible.

A Simple Way to Catch Most Bad Emails

That being said, there are two rules that can be applied to the majority of emails and used to weed out about 95% of incorrectly formatted addresses. They are:

  1. An email address must contain the ‘@’ symbol
  2. An email address must contain at least one period

Using these two rules, we can create a filter in Excel that will locate many of the invalid and poorly formatted email addresses in our list. We can also use data validation to catch an email address that doesn't match the validation criteria and circle invalid data.

Let’s send your first free
API
Email Verification API
call
See why the best developers build on Abstract
Get your free api

Create a Custom Autofilter to Find Invalid Email Addresses

Let’s use these two rules to create a custom autofilter in Excel. This will be a good way to do the first pass of our email list and remove the majority of invalid email addresses.

Locate the email address column and select the header of that column

Next, open the Data menu and click Filter. You should see a dropdown icon appear to the right of your selected column header.

Open the dropdown in the column header, and click Text Filter. This will open a sub-menu. In that menu, choose Custom Filter…

A pop up will open with the Custom Autofilter dialogue inside it. In the dropdown on the left, choose Does not contain, and in the input box to the right of the dropdown, type the ‘@’ symbol. Next, check the ‘Or’ radio button to bring up another filter parameter. Create the second parameter the same way you did the first, choosing Does not contain and the ‘.’ symbol. Click OK.

Excel will filter the column and only show you only email addresses that match these two criteria (in other words, the invalid email addresses.)

Use Data Validation to Find Invalid Email Address

The data validation method is slightly more complicated, but it will catch more invalid addresses and will also flag emails for you in real time as you type them into the spreadsheet.

Click the letter header above the column that contains your emails to highlight the entire column of addresses.

From the Excel Ribbon at the top of the application, click Data. This will open a panel with four subpanels. In the Data Tools subpanel, click the triangle next to Data Validation.

A popup containing the data validation rule dialogue will open. From the Allow dropdown menu, select Custom, and in the Formula field, paste the following code:




=AND(ISERROR(FIND(" ",A1)),LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1,IFERROR(SEARCH("@",A1)<SEARCH(".",A1,SEARCH("@",A1)),0),NOT(IFERROR(SEARCH("@",A1),0)=1),NOT(IFERROR(SEARCH(".",A1,SEARCH("@",A1))-SEARCH("@",A1),0)=1),LEFT(A1,1)<>".",RIGHT(A1,1)<>".")

Click Okay. Next, click the small triangle next to Data Validation again and select Circle Invalid Data.

The application may take a few moments if your email list is very large. Eventually, the invalid emails will be circled in red.

Finding Invalid Emails Using Bulk Email Validation

The above methods are fine for isolating the majority of syntax errors and typos. However, they will not help you find email addresses that have been abandoned, whose top-level domains have ceased use, that is spam, or have invalid MX or SPF records.

The only way to weed out these types of invalid email addresses is to use a third-party service like AbstractAPI’s Free Email Validator. The API has an endpoint that can be used to validate emails as they come in on your landing page or website, and it also provides the option to upload a CSV file containing a list of email addresses for bulk validation.

Let’s take a look at how to do that now.

Get Started With the API

Before you can use the API, you’ll need to sign up with an email address and password to get an API key. The key authenticates you to the API and allows you to make requests. Don’t worry: the API is completely free and will not send you spam or updates. Go to the API homepage and click on the blue "Get Started" button.

If you've never used AbstractAPI before, you'll be asked to create an account using your email address and a password. If you have used the service before, you may need to log in.

You'll land on the API dashboard, which contains links to documentation and pricing, and your API key.

Click  "Bulk upload (CSV)" on the left side to be taken to the bulk upload page.

Click the blue "Upload CSV" button. A dialogue box will open with a file browser in it.

Using the file browser, choose the CSV file that contains your mailing list and click "Upload CSV." Depending on the size of your mailing list, you may see a loading indicator while the upload process. Once the file has finished uploading, you’ll see a success box telling you that the file was received.

The results will be emailed to the address from your AbstractAPI account.

If the file is quite small, you should receive the email almost immediately. For larger files, it may take a few minutes.

Inside the email, you'll see a button to download the CSV file that contains your results. You will also be provided with a link to a hosted version of the file. This can be used to share your results with team members.

You can open the file in any spreadsheet program, including Excel.

The file will contain a quality score for each address, and values that indicate whether the address is formatted correctly, whether it is a free email, a disposable email, a role-based account, a catchall email, or whether domain MX records were found, and whether the SMTP is valid. It will also tell you whether or not the address can be delivered.

Conclusion

Purging invalid email addresses from your mailing list is easy using Excel filters and data validation. However, these methods are not as robust as using a dedicated third-party email validation service like AbstractAPI. These validation services can tell you not only if an address is spelled correctly, but whether it can actually be delivered or not.

FAQs

How do I validate an email address?

There are many ways to validate an email address, including checking the syntax against a regular expression, looking up the MX and SPF records of the email domain, pinging the server to assess deliverability, and checking the address against a blacklist of known spam accounts.

Many services out there provide APIs to run these checks against bulk email files, helping you to filter the invalid email addresses out of your mailing lists.

How do I validate an address in Excel?

To validate an email address in Excel, you can use filters and data validation. Filters are an easy way to add rules against which every entry in a column will be checked. When the filter is applied, only rows that match the filter rules will be shown.

Using data validation is a little more complicated, but is a slightly more robust method of validating email addresses. You can add a custom function to search strings inside the cells and highlight strings that do not match the formula rules.

How do I know if an email address is invalid?

It’s impossible to tell whether an email address is invalid simply by looking at it. The best way to determine if an address is invalid is to run it through a dedicated service that checks for invalid email addresses. These services check MX records, top-level domains, deliverability, and known spam blacklists to weed out invalid addresses.

One simple way to check for an invalid email address is to look for the ‘@’ symbol and ‘.’ symbol. An email address must contain at least these two things in order to be considered valid. However, this alone is not enough to tell you whether or not an address is valid.

4.4/5 stars (19 votes)

Elizabeth (Lizzie) Shipton
Lizzie Shipton is an adept Full Stack Developer, skilled in JavaScript, React, Node.js, and GraphQL, with a talent for creating scalable, seamless web applications. Her expertise spans both frontend and backend development, ensuring innovative and efficient solutions.
Get your free
Email Verification API
API
key now
Validate email instantly using Abstract's email verification API.
get started for free

Related Articles

Get your free
API
Email Verification API
key now
4.8 from 1,863 votes
See why the best developers build on Abstract
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
No credit card required