In this article:

Data Validation for Email Addresses in Google Sheets (2024)


Click here to Send an Email From a Lido Spreadsheet

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!]

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started