Data Validation for Email Addresses in Google Sheets (2023)
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.
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.
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.
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.
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.
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.
Whatever the method you choose, you will be able to get the same result:
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
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
