How to Find Duplicates in Excel: Methods, Tools, and What to Consider

Duplicate data is one of the most common problems in spreadsheets. Whether you're managing a customer list, a product inventory, or financial records, duplicate rows or values can quietly distort your analysis, inflate totals, and lead to real errors. Excel gives you several ways to find — and deal with — duplicates, each suited to different situations.

What Counts as a Duplicate in Excel?

Before diving into methods, it's worth clarifying what "duplicate" actually means in context, because Excel doesn't always know.

• A full-row duplicate means every cell in two rows contains identical values.
• A partial duplicate means one or more columns match — say, the same email address appears twice, even if the names differ slightly.
• A case-sensitive duplicate treats "Smith" and "smith" as different — Excel's default tools generally don't.

Knowing which type you're looking for shapes which method you should use.

Method 1: Conditional Formatting to Highlight Duplicates

This is the fastest visual approach and requires no formulas.

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

Excel will immediately color any value that appears more than once in your selected range.

What it does well: It's instant and visual. Great for a quick scan of a single column like email addresses or product IDs.

What it doesn't do: It won't catch duplicates that span multiple columns, and it highlights all instances — including the first occurrence — so you can't immediately tell which one appeared first.

Method 2: The COUNTIF Formula

For more control, COUNTIF lets you flag duplicates with a formula you can filter, sort, or build on.

In a helper column next to your data, enter:

=COUNTIF($A$2:$A$100, A2) 

This counts how many times the value in A2 appears in the full range. Any result greater than 1 is a duplicate. You can then filter the column to show only values where the count exceeds 1.

To flag just the second and later occurrences (not the first), use:

=COUNTIF($A$2:A2, A2)>1 

This uses an expanding range, so it only returns TRUE once a value has appeared before.

What it does well: Gives you a filterable, sortable duplicate flag. Works well when you need to keep the first occurrence and identify only the extras.

What it doesn't do: Matching across multiple columns requires combining values or using more complex formulas.

Method 3: Remove Duplicates Tool

If your goal is to clean the data rather than just identify it, Excel has a built-in tool.

1. Click anywhere inside your data range or table.
2. Go to Data → Remove Duplicates.
3. Select which columns to check — Excel will consider a row a duplicate only if all selected columns match.
4. Click OK.

Excel removes the duplicate rows and tells you how many were deleted and how many unique rows remain.

⚠️ Important: This is destructive — it deletes rows permanently (unless you undo). Always work on a copy of your data, or at minimum confirm you have a backup before running it.

What it does well: Cleans large datasets quickly. Flexible about which columns define "duplicate."

What it doesn't do: It doesn't let you review the duplicates before deletion.

Method 4: Using UNIQUE and FILTER (Excel 365 / Excel 2021+)

Newer versions of Excel introduced dynamic array functions that make duplicate handling much more powerful.

• =UNIQUE(A2:A100) returns a list of distinct values from a range, automatically spilling results into adjacent cells.
• Combined with FILTER, you can extract only the duplicate values:

=FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1) 

This returns only the values that appear more than once — without modifying your original data.

What it does well: Non-destructive, dynamic, and updates automatically when your data changes. Ideal for ongoing data monitoring.

What it doesn't do: These functions aren't available in Excel 2016 or older versions, or in some versions of Excel for Mac depending on the subscription.

Comparing the Methods 🔍

Factors That Affect Which Method Works for You

Several variables determine which approach actually fits your situation:

Data structure: A single-column list is simple. A multi-column dataset where "duplicate" means matching on two or three specific fields requires a different approach — either using Remove Duplicates with specific columns selected, or combining fields in a helper column before applying COUNTIF.

Data volume: Conditional formatting on 50,000 rows can slow Excel down noticeably. Formula-based approaches or Power Query (available under Data → Get & Transform) scale better for large datasets.

Excel version: The UNIQUE and FILTER functions are only available in Excel 365 and Excel 2021. Users on older versions — 2016, 2019, or certain Mac builds — don't have access to these and need to rely on COUNTIF or the Remove Duplicates tool.

Whether you need to keep the data intact: If you're auditing rather than cleaning, any method that flags without deleting is safer. If you're preparing data for import or analysis, the Remove Duplicates tool is often faster.

Case sensitivity: Excel's built-in tools treat "APPLE" and "apple" as the same. If your data requires case-sensitive matching, you'll need a formula using EXACT combined with array logic — a more advanced approach.

What About Duplicates Across Multiple Columns?

This is where most basic tutorials fall short. If you need to find rows where, say, both the first name and email address match — but not necessarily the entire row — you need to define what combination makes something a duplicate.

A common workaround is creating a helper column that concatenates the relevant fields:

=A2&"|"&B2 

Then apply COUNTIF or conditional formatting to that helper column. This lets you define "duplicate" on your own terms.

Handling Duplicates in Excel Tables vs. Regular Ranges

If your data is formatted as an Excel Table (Insert → Table), the Remove Duplicates tool works the same way. Dynamic functions like UNIQUE also work within table references. One advantage of using tables: structured references make formulas easier to read and maintain as data grows.

The right method isn't just about which one works — it's about which one fits how your data is structured, what version of Excel you're running, and what you actually need to do with the duplicates once you find them.