Function Problems

How to Find Duplicate Email Addresses in Excel Columns

Camila Milosovich Oct 19, 2026 869 views

Question details

The user needs to identify, highlight, count, or extract repeated email addresses either within a single list or shared across multiple columns.

How to Find Duplicate Email Addresses Within or Between Excel Columns

Product: Excel
Device & OS: not provided
Scenario: Cleaning up mailing lists, finding overlapping contacts across datasets, or preventing duplicate outreach.
Observed behavior: Duplicate entries exist in contact lists, but it is difficult to visually identify or extract them without applying specific spreadsheet rules or functions.

Before you start

Before checking for duplicates, ensure your email lists are clean by using the TRIM function to remove accidental leading or trailing spaces, which could cause identical emails to appear unique.

Solution 1Recommended

Highlight Duplicate Email Addresses Within a Single Column

Use built-in Conditional Formatting to quickly visually locate all repeated email addresses within one specific list.

This is the fastest method if you just want to see where duplicates exist within your current worksheet. It will highlight both the original and the duplicated entry.

Select the Data Range

Click and drag to select the column or range of cells containing your email addresses.

Apply Conditional Formatting

Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and hover over 'Highlight Cells Rules'.

Choose Duplicate Values

Click on 'Duplicate Values...'. In the dialog box, select your preferred highlighting color scheme (e.g., Light Red Fill with Dark Red Text) and click 'OK'.

Highlight Duplicate Email Addresses Within a Single Column

Instant Results: All duplicated email addresses in the selected range will instantly be colored according to your formatting choice.

Solution 2

Find Shared Email Addresses Between Two Different Lists

Use the COUNTIF function to cross-reference two columns and determine if an email in List A also exists in List B.

Solution 3

Count or Extract Duplicates Using Advanced Formulas

For Microsoft 365 users, combine LET, UNIQUE, and FILTER functions to automatically generate a clean list of duplicate emails.

Manage Data Efficiently with WPS Office

Find and Handle Duplicate Emails Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful, user-friendly tools to instantly highlight or remove duplicate data. With full support for advanced array formulas, you can manage complex email lists without any hassle.

1. Open Your Email List: Launch WPS Spreadsheet and open the document containing your contact data.
2. Access the Data Tab: Highlight your email columns, then navigate to the 'Data' tab on the top menu ribbon.
3. Highlight or Remove Duplicates: Click 'Highlight Duplicates' to color-code repeated entries, or select 'Remove Duplicates' to instantly delete them and keep a clean list.

Fully compatible with Microsoft Excel formats (.xlsx) and formulas like COUNTIFOne-click 'Highlight Duplicates' tool directly accessible from the ribbonFree to use, lightweight, and perfect for organizing large contact lists

microsoft office alternative - wps office

Frequently Asked Questions

Why is Excel not finding exact duplicate email addresses?

This usually happens when there are hidden spaces at the beginning or end of the email address, or differences in text formatting. Use the TRIM() function in a helper column to clean the data before applying duplicate checks.

How can I remove the duplicate emails instead of just highlighting them?

To delete duplicates permanently, select your data range, go to the 'Data' tab, and click 'Remove Duplicates'. Check the box for the column containing your emails and click OK. Excel will keep the first instance and delete the rest.

Can I check for duplicate emails across multiple worksheets?

Yes. While standard Conditional Formatting only works on a single sheet, you can use the COUNTIF or VLOOKUP function to reference a range on another worksheet. For example: =COUNTIF(Sheet2!A:A, A2)>0.

Is conditional formatting case-sensitive for email addresses?

No, Excel's default duplicate highlighting rule and the COUNTIF function are not case-sensitive. 'User@Email.com' and 'user@email.com' will correctly be identified as duplicates.

Excel & Data Problems