If you’re using Google Sheets for email addresses, maybe for clients, customers, or employees, you’ll want to make sure the data is entered correctly. One way to do this is to validate the format to confirm it’s an email address.

We’ll show you two ways to do this in Google Sheets. First, you can use data validation to ensure the cell contains the @ (At) symbol. Second, you can use a function to confirm the email address has both the @ symbol and a valid extension. So, you can use whichever method is best for your situation.

Note: Neither method confirms that the email address is workable. Each simply confirms that the format is correct for an email address.

RELATED: How to Restrict Data in Google Sheets with Data Validation

Confirm Email Address Formats Using Data Validation

This first method uses data validation to look for the @ symbol in your selected cells. If the symbol isn’t found, you can display an error indicator on the cell or reject the data.

Select the cells where you want to apply the data validation. Then, click Data > Data Validation from the menu.

Select Data, Data Validation

Confirm the selection in the Cell Range box. For the Criteria, choose “Text” in the first drop-down box, “Contains” in the second, and then enter the @ symbol in the box on the right.

Enter the Data Validation Criteria

Next to “On Invalid Data,” decide if you want to show a warning or reject the input.

Choose an On Invalid Data option

If you choose Show Warning, you’ll see an error indicator (red triangle) on the top right corner of the cell. Hover your cursor over it to see the warning message.

Data Validation warning

If you choose Reject Input, you must include the @ symbol when typing in the cell. If you do not, then your text will be rejected. For this option, we recommend that you include help text. This can eliminate confusion to the user as to why their data violates the cell’s rule and isn’t being accepted.

Rejected data default message

Check the box next to Appearance for “Show Validation Help Text.” You can then use the default message or type in a custom message.

Data Validation custom message

Then, when invalid data is entered, you’ll see a pop-up message with that help text.

Data rejected message

Click “Save” when you finish setting up the data validation.

Confirm Email Address Formats Using a Function

While checking for the @ symbol to validate the format of an email address is good, you may need to go a step further. For instance, you may have the @ symbol but no extension such as Myemail@gmail . As you can see, the .com is missing.

By using the ISEMAIL function, you can check for both the symbol and the extension. The function will return a simple true or false response.

Select a cell where you want to enter the formula and display the response. Type the following replacing the B2 cell reference with your own:


Enter the ISEMAIL function

Press Enter or Return and you should then see TRUE for a valid email address and FALSE for an invalid one.

Function response

The function checks for the following extensions:

  • .com
  • .net
  • .org
  • .edu
  • .gov
  • .info

If you have an entire column of email addresses that you want to check, as in our example, you can use the fill handle to copy the function to the remaining cells.

Copy the function with the fill handle

Then, you’ll see TRUE or FALSE for all emails in that column of your sheet. As examples, we’ve highlighted an email address without the symbol or extension, with only the symbol, with only the extension, and one with both making it a valid format. You can see in the screenshot below, the first three are invalid (False) and the fourth is valid (True).

Email function responses

If you don’t want to search for an add-on or other third-party tool to confirm the formats for email addresses in Google Sheets, give one of these quick methods a try.

RELATED: The Best Google Sheets Add-Ons

And if you’re concerned about duplicate emails or other data in your spreadsheet, take a look at how to remove duplicates in Google Sheets.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »