How to validate emails in Google Sheets

Want to verify Email Addresses in Google Sheets? If you want to know how to validate emails in Google Sheets, then this post is going to help you. Working with spreadsheets often involves a lot of data. This data sometimes include a list of email addresses that need to be validated properly. While you can manually look at the emails and verify their correctness, it could be quite a task if the list comprises a lot of emails, ranging from a hundred to thousands or even more. This post discusses how you can use function, data validation, and custom formula (under conditional formatting) in Google Sheets to automatically validate email addresses. The post also suggests ways to highlight the incorrect emails and prompt the reader with an error message. So let’s begin.

How to validate emails in Google Sheets

We are going to show you how to validate emails in Google Sheets using the following methods:

Using the ISEMAIL function
Using data validation rule
Using conditional formatting rule

Let us see each of these in detail:

1] Validate emails in Google Sheets using the ISEMAIL function

If you have a long list of emails, you can evaluate whether these emails are even real or not, using the ISEMAIL function.

Suppose we have a list of emails in cell range A2:A5 in a given spreadsheet. To evaluate whether these emails are true or not, we are going to follow the steps mentioned below:

Place the cursor in cell B2.
Type =ISEMAIL(A2).
Press the Enter key. The function will return TRUE or FALSE, indicating whether the email is valid or not.
Again place your cursor in cell B2.
Move the mouse pointer to the bottom-right corner of the cell. As it converts to a + sign, drag it downwards till cell B5.

Now let’s see how to use color coordination to highlight cells.

Select the cell range B2:B5.
Go to Format > Conditional formatting.
Under Format rules, select Format cells if > Text contains, then enter FALSE.
Select a Fill Color under Formatting style.
Click on the Done button. As you can see in the above screenshot, we have highlighted invalid emails in Yellow color.

2] Validate emails in Google Sheets using Data Validation rule

Data validation is a built-in mechanism in Microsoft Excel and Google Sheets that lets you enter only specific data within a cell by applying rules. The validation ensures that the data entered should comply with the rule. Let us see how to impose a data validation on a spreadsheet column that needs to have only emails.

In the same spreadsheet shown in the above example, select cell range B2:B5.
Click on the Data menu.
Select the Data validation option.
Within the Data validation window, select Text > is valid email under Criteria.
Click on the Save button.

The data validation creates red triangles in the upper-right corner of the cells having incorrect inputs. As you move your mouse pointer over those triangles, you will see the error message.

If you want to validate data while it is being entered and display a custom error message for an incorrect input, select On invalid data > Reject input. Then click on the checkbox next to Appearance and enter/ edit the error message you want to display to the reader.

Also Read: How to Export or Import Data from Excel to Google Sheets.

3] Validate emails in Google Sheets using Conditional Formatting rule

Conditional formatting allows you to change the appearance of a cell based on a certain rule. So when you want to highlight cells that contain invalid email addresses, you can use custom formula under conditional formatting in Google Sheets. Here’s how this can be done using the above example.

Select cell range B2:B5.
Go to Format > Conditional formatting.
Under Format rules, select Format cells if > Custom formula is, then enter the following formula:
```
=NOT(ISNUMBER(MATCH("*@*.?*",A2,0)))
```
Select a Fill Color under Formatting style.
Click on Done. Invalid emails will be highlighted in yellow color.

This winds up three different methods for validating emails in Google Sheets. I hope you find this useful.

How do you add validation in Google Sheets?

Google Sheets allows you to add data validation rules to verify the data or information that is being entered in a cell. To add validation, select the cell(s) and click on the Data menu on top of the spreadsheet. Then select the Data validation option. Now in the Data validation window, set validation criteria using the available options (Number Text, Date, etc.). Then specify whether to show a warning or reject input based on the set criteria. You can also display a custom error message for invalid input. Click on Save to add validation. That’s it!

How do I make an email link in Google Sheets?

To create a link on an email in Google Sheets, select the cell containing the email address and then click on the Insert menu. Then select the Link option. Alternatively, you can press the Ctrl + K key combination. Now in the text box area, type ‘mailto:<emailadderss>‘ and click on the Apply button.