How to Find Duplicates in Excel (5 Ways)
Five reliable ways to find duplicates in Excel — Conditional Formatting, COUNTIF, Remove Duplicates, Pivot Tables, and a free online highlighter — with the exact clicks and formulas for each.
Founder of ARCED Foundation, ARCED International, and Solutions of Things Lab (SoTLab). Built FolderManifest to help teams protect file integrity and stay audit-ready.
Quick Answer
The fastest built-in way to find duplicates in Excel is Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. To count them, use COUNTIF. To delete them, use Data → Remove Duplicates. If you would rather not build rules, drop the file into the free Spreadsheet Duplicate Finder and duplicates light up automatically.
Method 1: Conditional Formatting (Highlight Duplicates)
Conditional Formatting is the most visual way to spot duplicates and the one most people reach for.
- Select the column or range you want to check.
- On the Home tab, click Conditional Formatting.
- Choose Highlight Cells Rules → Duplicate Values.
- Pick a highlight color and click OK.
Excel highlights every cell whose value appears more than once. For a step-by-step on highlighting whole rows and two-column matches, see how to highlight duplicates in Excel.
Method 2: COUNTIF (Count the Duplicates)
To flag and count duplicates in a helper column, with values in column A starting at row 2:
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")
=COUNTIF(A:A, A2) ' how many times this value appearsCOUNTIF is handy when you need a number — for example, which email appears most often — rather than just a highlight.
Method 3: Remove Duplicates Button
Excel has a one-click way to delete duplicate rows:
- Select your data (including headers).
- Go to the Data tab and click Remove Duplicates.
- Tick the columns that define a duplicate, then click OK.
Back up first
Remove Duplicates deletes rows permanently. Always copy the sheet first, and find the duplicates before you remove them so you know exactly what is leaving.
Method 4: Pivot Table (Count Occurrences)
A Pivot Table is a non-destructive way to see how many times each value appears without touching your data. Insert a Pivot Table, drag the column into both Rows and Values, and set the Values field to Count. Any value with a count greater than 1 is a duplicate. This is ideal for a quick frequency report on a large list.
Method 5: Free Online Tool (No Excel Skills Needed)
If you do not want to build rules or formulas — or you are on a machine without Excel — the free Spreadsheet Duplicate Finder highlights duplicate values in an .xlsx or .csv file the moment you drop it in. It runs entirely in your browser, so your data is never uploaded.
Which Method Should You Use?
| Need | Best method |
|---|---|
| See duplicates highlighted | Conditional Formatting (or the online tool) |
| Count occurrences | COUNTIF or Pivot Table |
| Delete duplicate rows | Data → Remove Duplicates |
| No Excel / instant highlight | Free online tool |
Frequently Asked Questions
How do I find duplicates in Excel quickly?
Select your data, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and choose a color. For a no-setup option, upload the file to the free Spreadsheet Duplicate Finder.
How do I highlight duplicates in Excel?
Use Conditional Formatting, or for rows and two-column matches see how to highlight duplicates in Excel.
How do I remove duplicates in Excel?
Select your data, go to the Data tab, and click Remove Duplicates. See how to remove duplicates in Excel & CSV for the safe workflow.
Can I find duplicates across two Excel sheets?
Yes — use =COUNTIF(Sheet2!A:A, A2) > 0, or compare two files directly with the Compare Excel Files tool.
Find Your Excel Duplicates Now
Skip the rules and formulas — drop your spreadsheet into the free, private highlighter.