How to check if the email address is valid or not in Excel?

In managing Excel databases, it’s common to need verification that every email address is correctly formatted before sending communications or importing contact lists. Invalid email addresses can lead to bounced messages, incomplete correspondence, or errors in integrated systems. Rather than manually checking each entry, Excel provides practical ways to automatically review email addresses for validity, simplifying cleanup and ensuring higher accuracy in your data management workflow.

Check if the Email address is valid or not with formula

VBA Code – Automatically validate email addresses

---

Check if the Email address is valid or not with formula

To efficiently check whether an email address in your worksheet is likely to be valid, you can use an Excel formula. This approach is ideal when you need to quickly scan a large list of emails and highlight entries that might not meet basic email format requirements.

The formula below examines the email address to ensure it contains at least one period (“.”) and that there is a period following the “@” symbol, which are both expected in valid email formats.

1. Enter or copy the below formula into a blank cell where you want to return the checking result:

2. After entering the formula, press Enter to confirm. Then, drag the fill handle down to apply this formula to other cells in your target column. The formula will return TRUE for entries that pass the check (likely valid) and FALSE for entries that do not meet these requirements.

Notes and tips:

• This formula checks only basic format: It confirms the presence of periods and the location relative to the "@" symbol, but does not guarantee the domain or username exists, nor filter out some rare but valid cases.
• If your data includes spaces, special characters, or trailing punctuation, these could result in an incorrect validity check.
• For more rigorous email format checking, consider adding supplementary checks or using VBA/macros as described below.

---

VBA Code – Automatically validate email addresses

For more advanced and automatic email validation, especially if you want to flag or highlight invalid addresses programmatically, using a VBA macro can be very effective. This approach is suitable for spreadsheets with many email records or when batch processing is needed to ensure compliance with communication protocols.

This code checks whether the email addresses in a selected range meet basic formatting standards (presence of "@" and at least one "." after the "@"). Optionally, it can flag the invalid cells by coloring them or adding a comment for easier review during data cleaning.

1. Click Developer Tools > Visual Basic, then in the Microsoft Visual Basic for Applications window, click Insert > Module and paste the following VBA code into the module:

Sub ValidateEmailAddresses()
    Dim rng As Range
    Dim cell As Range
    Dim email As String
    Dim atPos As Long
    Dim dotPos As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select email range", xTitleId, Selection.Address, Type:=8)
    
    For Each cell In rng
        email = Trim(cell.Value)
        atPos = InStr(1, email, "@")
        
        If atPos > 1 Then
            dotPos = InStr(atPos + 1, email, ".")
            
            If dotPos > atPos + 1 Then
                cell.Interior.ColorIndex = xlNone ' Format as valid
            Else
                cell.Interior.Color = vbYellow ' Flag as invalid
                cell.AddComment "Invalid email format"
            End If
        Else
            cell.Interior.Color = vbYellow ' Flag as invalid
            cell.AddComment "Invalid email format"
        End If
    Next cell
End Sub

2. To run the macro, click the button. A dialog will prompt you to select the range containing your email addresses. Invalid entries will be highlighted in yellow and a comment will be added ("Invalid email format"), while valid entries remain unchanged.

• If your worksheet is protected, you may need to unprotect it before running the macro. Save your work first.
• Remove or clear comments before re-running to avoid duplicate flags.
• The macro does not verify email existence, only formatting. For organization-wide checks, consider exporting results for further verification if needed.

By using these Excel solutions, you can reduce manual effort when managing emails, minimize errors in communication, and streamline preparation of your contact lists for email campaigns or reporting.

• If you encounter errors due to cell formats (such as numbers stored as text), ensure your email column is formatted as General or Text before applying formulas or validation.
• For large datasets, consider combining formula checks and VBA flagging for thorough review.
• Periodically audit your database for changes in domain requirements or new email format standards.

More relative articles: