How to Find Duplicates in Excel
Learn 5 easy methods to identify and remove duplicate values in your Excel spreadsheets
Quick Answer
The fastest way to find duplicates: Select your data range → Go to Home tab → Click Conditional Formatting → Choose Highlight Cells Rules → Select Duplicate Values. Excel will highlight all duplicate cells automatically.
For removal: Select data → Go to Data tab → Click Remove Duplicates → Choose columns to check → Click OK.
Method 1: Conditional Formatting (Visual Highlighting)
Step-by-Step Instructions:
- 1.Select the range of cells you want to check for duplicates
- 2.Click the Home tab in the ribbon
- 3.Click Conditional Formatting in the Styles group
- 4.Hover over Highlight Cells Rules
- 5.Click Duplicate Values
- 6.Choose formatting style (default is Light Red Fill) and click OK
Pro Tip: This method highlights duplicates but doesn't remove them. Perfect for reviewing before deletion.
Best For:
- Visual identification of duplicates
- Reviewing data before removing duplicates
- Highlighting both occurrences of duplicate values
- Quick visual audit of data quality
Method 2: Remove Duplicates Tool (Fast Removal)
Step-by-Step Instructions:
- 1.Select your data range (including headers if applicable)
- 2.Click the Data tab
- 3.Click Remove Duplicates in the Data Tools group
- 4.Check/uncheck columns to include in duplicate detection
- 5.Click OK
- 6.Excel shows a message with how many duplicates were removed
Warning: This permanently deletes duplicate rows. Make a backup copy before using this method!
Best For:
- Quickly cleaning large datasets
- Removing entire duplicate rows
- Working with multiple columns as duplicate criteria
- One-time data cleanup tasks
Method 3: COUNTIF Formula (Advanced Control)
Formula Example:
This formula returns TRUE if the value in A2 appears more than once in the range A2:A100.
Step-by-Step Instructions:
- 1.Add a helper column next to your data (e.g., column B)
- 2.In B2, enter:
=COUNTIF($A$2:$A$100,A2)>1 - 3.Copy formula down to all rows
- 4.TRUE values indicate duplicates, FALSE means unique
- 5.Filter or sort by the helper column to group duplicates
Best For:
- Keeping all data while flagging duplicates
- Creating custom duplicate detection logic
- Integrating with other formulas
- Preserving original data structure
Method 4: Advanced Filter (Copy Unique Values)
Step-by-Step Instructions:
- 1.Select your data range
- 2.Click Data tab → Advanced (in Sort & Filter group)
- 3.Choose "Copy to another location"
- 4.Check "Unique records only"
- 5.Specify where to copy unique values
- 6.Click OK
Best For:
- Extracting unique values to a new location
- Preserving original data
- Creating clean datasets from messy data
- One-time unique value extraction
Method 5: VBA Automation (For Power Users)
For frequent duplicate removal or complex scenarios, automate the process with VBA macros. Our AI-powered VBA Generator can create custom code for your specific duplicate detection needs.