In this article:

Data Validation for Email Addresses in Google Sheets (2024)

May 8, 2024

In this article we will show how to validate emails using data validation in google sheets in just a few simple steps. Simply follow the steps below:

Validate Emails using Data Validation in Google Sheets

1. Identify the Column Containing the Email Addresses

For our example, we have two columns: Column A for the names and Column B for the emails.

The cells containing the emails in Column B start at B2. We then set Column C as the column for storing the formula for validating the emails listed in Column B.

google sheets data validation email address

2. Insert our Email Validation Formula 

The formula for validating the emails is

=IF(REGEXMATCH(cell,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"), "Valid", "Invalid")

Where cell is the reference to the cell containing the email to be validated.

This formula checks if the email contains a valid top level domain such as .com, .net etc. In the case of Gmail, it makes sure it is gmail.com instead of just gmail. Incorrect top level domains is a common mistake when typing email addresses.

google sheets email validation formula

3. Press Enter

Once you press Enter, Google Sheets will analyze the email and determine if its format is complete with the domain and the top-level domain. It will display Valid if it is the case, while it will display Invalid if it is not the case. 

google sheets valid email - Formula output shown

4. Apply to Other Cells in the Column

Since we know that the formula works well, we can now copy the formula to other cells in the column. There are three ways to do so:

Autofill: The Autofill function appears when you add a formula for the first time in a cell that is part of a column. Google Sheets makes a suggestion of the additional range where you may add the formula. Click the Check symbol if the range matches well with what you want.

Autofill to validate other email addresses

Double-click the Lower-Right Corner of the Cell: When you select a cell, a highlight box will appear. The blue highlight box has a small circle on its lower-right corner. Double click it to cover the other rows in the column.

validating email addresses in google sheets

Click and Drag the Lower-Right Corner of the Cell: When you select a cell, a highlight box will appear. The blue highlight box has a small circle on its lower-right corner. Click and drag it to cover the other rows in the column.

Click and drag to validate other email addresses‍

Whatever the method you choose, you will be able to get the same result:

google sheets valid email‍

You can now validate the format of the email addresses in Google Sheets!

Google Sheets Email Validation

Email validation in google sheets is easy. Simply use our custom formula below:

=IF(REGEXMATCH(cell,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"), "Valid", "Invalid")


That's it. If the email is valid it will return VALID, otherwise it will return INVALID.

FAQs

Can It Validate Domain Names?

No. It can only check if the domain is well-formatted. It means that it only checks if the format

username@domain.com

Is followed instead of username@domain, for example.

Can It Check if the Email Address is Functional?

It is possible that the email address format is followed, but the domain itself and/or the username does not exist. For that, you ultimately have to send a test email and wait for a response to see if it is received by the recipient.

If you enjoyed this article, check out some of our other popular articles:

Hyperlink an Email Address in Google Sheets

Email CSV to Google Sheets

Send Email From Google Sheets [The Right Way!]

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->