How to Find Duplicates in Excel

The fastest way to find duplicates in Excel is Conditional Formatting:

1. Select the range you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a highlight color.
4. Click OK.

That highlights duplicate values without deleting anything. If you need a filterable review column, use COUNTIF instead:

=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")

Use this guide when you want to find and review duplicates first. When you are ready to delete them, use How to Remove Duplicates in Excel.

1. Choose the Right Duplicate Check

Not every duplicate problem is the same. Start by deciding what "duplicate" means in your sheet.

The most important question is: which column or columns define a duplicate? For customers, it might be Email. For orders, it might be Order ID. For survey exports, it might be Respondent ID plus Timestamp.

2. Example Data

Suppose you have this customer list:

There are two possible duplicate questions:

The answer depends on the business rule. One repeated email might be a true duplicate, or it might be a shared team inbox.

3. Highlight Duplicates with Conditional Formatting

Use this when you want to visually scan duplicates without changing the data.

Steps:

1. Select the range, such as A2:A100.
2. Go to Home > Conditional Formatting.
3. Choose Highlight Cells Rules > Duplicate Values.
4. Pick a format, such as a red fill.
5. Click OK.

Excel highlights all values that appear more than once.

This is best for quick review, but it does not create a filterable status column. If you need to sort or filter duplicate rows, use a helper formula.

For more visual rule examples, see Excel Conditional Formatting.

4. Find Duplicates with COUNTIF

Use COUNTIF when you want a helper column that says Duplicate or Unique.

=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")

Result:

How it works:

For more COUNTIF examples, see the Excel COUNT function tutorial. If you are deciding between COUNTIF and COUNTIFS, see COUNTIF vs COUNTIFS in Excel.

5. Show Only the Second and Later Duplicates

Sometimes you want to keep the first occurrence and flag only later repeats. Use a growing range:

=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","First")

Result:

This is useful when you plan to keep the first row and review only the repeats.

6. Find Duplicate Rows Across Multiple Columns

If a duplicate is defined by more than one column, use COUNTIFS.

Example: find rows where both Customer ID and Email repeat:

=IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"Duplicate row","Unique")

This checks both criteria at the same time.

If the formula gets hard to read, create a helper key:

=A2&"|"&B2

Then run COUNTIF against the helper key column.

7. Find Duplicates Between Two Lists

If you have two separate lists and want to find values that appear in both, use the same comparison pattern as a found/missing check:

=IF(COUNTIF($D$2:$D$100,A2)>0,"Also in List 2","Only in List 1")

Use this when comparing customer lists, product IDs, invoice numbers, or exports from two systems.

For the full workflow, see How to Compare Two Columns in Excel.

8. Filter and Review Duplicate Rows

After adding a helper column, turn on filters:

1. Select your table.
2. Go to Data > Filter.
3. Open the filter on the helper column.
4. Select only Duplicate or Repeat.
5. Review those rows before deleting anything.

This review step matters because not every repeated value is a mistake.

9. Common Reasons Duplicates Are Missed

Excel compares values exactly. If duplicates are not found, check the data first:

For a broader cleanup workflow, see Excel Data Cleaning. When inconsistent punctuation or labels need a one-time standardization, use Find and Replace in Excel before checking duplicates again.

10. Find Duplicates Without Removing Them

Use this workflow when the data is important:

1. Add a helper column with COUNTIF or COUNTIFS.
2. Filter to Duplicate or Repeat.
3. Review the business meaning of each repeated row.
4. Copy the duplicate rows to a review sheet if needed.
5. Remove duplicates only after you know which rows are safe to delete.

This keeps the review step separate from the deletion step.

11. When to Remove Duplicates

Finding duplicates and removing duplicates are different tasks.

Do not jump straight to deletion if the duplicate definition is unclear. Find and review the duplicates first.

12. Practice Example

Use this table:

Tasks:

Reference answers:

13. Summary

To find duplicates in Excel, use Conditional Formatting for a quick visual check, COUNTIF for a filterable helper column, and COUNTIFS when a duplicate depends on multiple columns. Clean spaces and inconsistent formats before trusting the result, then review duplicate rows before using the Remove Duplicates tool.

14. Related Guides

• Delete duplicate rows safely with How to Remove Duplicates in Excel.
• Compare two lists with How to Compare Two Columns in Excel.
• Clean messy data before checking duplicates with Excel Data Cleaning.
• Highlight duplicate values visually with Excel Conditional Formatting.
• Learn more COUNTIF and COUNTIFS patterns in the Excel COUNT function tutorial.
• Choose between single-condition and multi-condition counts with COUNTIF vs COUNTIFS in Excel.