How to Check Duplicate Values in Excel

Duplicate data is one of the most common problems in spreadsheets — and one of the most consequential. Whether you're managing a customer list, reconciling invoices, or cleaning up survey responses, undetected duplicates can skew your analysis and lead to real mistakes. Excel offers several built-in ways to find, highlight, and deal with duplicate values, each suited to different situations.

Why Duplicates Happen (and Why They Matter)

Duplicates creep in through manual data entry, merged datasets, copy-paste errors, or imported files from external systems. A single duplicated row might seem harmless, but in a dataset of thousands of records, duplicates can inflate totals, corrupt pivot tables, and produce misleading reports.

Before deciding how to handle duplicates, it helps to understand what you're actually looking for — exact full-row duplicates, repeated values in a single column, or near-matches that represent the same real-world entity.

Method 1: Conditional Formatting to Highlight Duplicates 🔍

The fastest visual method — no formulas required.

1. Select the column or range you want to check
2. Go to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values
3. Choose a highlight color and click OK

Excel immediately colors any cell whose value appears more than once in your selected range. This works well for a quick audit of a single column — like email addresses, order IDs, or product codes.

Important limitation: Conditional formatting checks individual cells, not entire rows. Two rows might share the same email address but have different names — that would be flagged. Or two rows might be fully identical across all columns — that requires a different approach.

Method 2: COUNTIF Formula for Precise Duplicate Detection

For more control, use a formula. The COUNTIF function counts how many times a value appears in a range.

In a helper column next to your data, enter:

This returns the number of times the value in A2 appears in the full range. Any result greater than 1 means that value is duplicated. You can then filter by that helper column to isolate and review duplicates.

To flag duplicates more explicitly, wrap it in an IF statement:

This approach scales well and gives you a column you can sort, filter, or use in further analysis.

Method 3: Remove Duplicates Tool

If your goal is deletion rather than review, Excel's built-in Remove Duplicates tool is direct.

1. Click anywhere inside your dataset
2. Go to Data → Remove Duplicates
3. Select which columns to check — Excel flags rows as duplicates only if every selected column matches
4. Click OK

Excel will tell you how many duplicate rows were found and removed, and how many unique rows remain.

⚠️ Critical note: This action is permanent unless you undo immediately. Always work on a copy of your data, or create a backup before running Remove Duplicates on anything important.

The column selection step is where judgment matters. Checking all columns finds truly identical rows. Checking only the email column removes any row where that email has already appeared — which may or may not be what you want, depending on your data structure.

Method 4: Advanced Filter for Unique Records

Less commonly used but worth knowing — Excel's Advanced Filter can extract only unique rows to a new location, leaving your original data untouched.

1. Select your data range
2. Go to Data → Advanced
3. Choose Copy to another location
4. Check Unique records only
5. Specify where to paste the results

This is non-destructive and useful when you want to work with a deduplicated version of your data while preserving the original.

Method 5: Pivot Tables as a Duplicate Detector

A pivot table won't highlight duplicates directly, but it reveals them clearly. Drop your column of interest into both the Rows area and the Values area (set to Count). Any value with a count greater than 1 is duplicated.

This method is particularly useful when you want to understand the scale of duplication — not just whether duplicates exist, but how many times each value appears.

Comparing the Methods

Variables That Affect Which Method Works Best

The right approach depends on several factors that vary by user and dataset:

• Dataset size — Conditional formatting can slow down significantly on very large files; formulas or Power Query handle scale better
• Data structure — Single-column duplicates versus full-row duplicates require different logic
• Excel version — Some features behave differently between Excel 2016, 2019, Microsoft 365, and the web version; newer versions support dynamic array functions like UNIQUE that older versions don't
• What you want to do with duplicates — Flag them, count them, remove them, or extract the clean list
• Whether the original data must be preserved — This rules out the Remove Duplicates tool unless you're working on a copy

The UNIQUE function (available in Microsoft 365 and Excel 2021) offers a modern alternative — it returns a list of distinct values directly, without needing helper columns or manual filtering. But it's only available in newer versions, so the method you can actually use depends on what you're running. 🖥️

Understanding which duplicates matter in your specific dataset — and what outcome you're trying to reach — is what determines which of these tools is actually the right one for your situation.