Can Excel Find Duplicates?

Yes, Excel can find duplicates. There are a few different built-in ways to do it, depending on whether you want to highlight them, count them, or remove them entirely.

Contents:

1. Quick Answer

2. How to Highlight Duplicates with Conditional Formatting

3. How to Find Duplicates with a Formula

4. How to Remove Duplicates with the Built-In Tool

Quick Answer

Excel can find duplicates using Conditional Formatting, a COUNTIF formula, or the built-in Remove Duplicates feature. Conditional Formatting is the fastest visual method. COUNTIF gives you the most control. Remove Duplicates deletes them in one click.

How to Highlight Duplicates with Conditional Formatting

This is the quickest way to see duplicates at a glance. Select the range of cells you want to check, then go to:

Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

A dialog box will appear letting you choose how to color the duplicates. Click OK and Excel immediately highlights every cell that appears more than once in the selection.

You can also flip the dropdown from "Duplicate" to "Unique" in the same dialog if you want to highlight the one-of-a-kind values instead.

How to Find Duplicates with a Formula

If you need more control, a COUNTIF formula lets you flag duplicates row by row. This is useful when you want to keep your data intact but add a helper column showing which rows are repeated.

= COUNTIF(range, value) > 1

range - The full column or range you want to check (lock it with $ signs so it doesn't shift when you copy the formula down). value - The specific cell you're checking against the range.

So in practice, if your data is in column A starting at A2, you'd put this in B2 and drag it down:

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

This returns TRUE for any value that appears more than once and FALSE for values that appear only once.

If you'd rather see a count than TRUE/FALSE, just drop the > 1 part. The formula will return how many times that value appears in the range.

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

You can learn more about building formulas like this in The Basics to Creating and Using Formulas in Excel.

How to Remove Duplicates with the Built-In Tool

If you don't just want to find duplicates but actually delete them, Excel has a dedicated Remove Duplicates tool.

Click anywhere in your data, then go to:

Data > Remove Duplicates

A dialog will appear listing all your columns. Check the columns Excel should use when deciding what counts as a duplicate. For example, if you only check "Last Name," a row is a duplicate if the last name repeats, even if other columns differ. Check all columns to only remove rows that are identical across every field.

Click OK and Excel tells you how many duplicate rows it removed and how many unique rows remain.

One thing to keep in mind: this tool permanently deletes rows. It's a good idea to work on a copy of your data, or use How to Remove Duplicates in Excel as a reference for the full walkthrough before you run it on anything important.

For tidying up data more broadly, pairing Remove Duplicates with How to Remove Extra Spaces from Text can help catch cases where "John Smith" and "John Smith " look the same to the eye but register as different values to Excel.