How to Remove Duplicates in Excel

Easy The Built-in Remove Duplicates Tool

For most scenarios, Excel’s built-in feature gets the job done quickly:

1. Select your data range (including headers)
2. Go to Data tab → Remove Duplicates
3. Choose which columns to check for duplicates
4. Click OK

Excel will tell you how many duplicates were found and removed, keeping the first occurrence of each unique record.

Source: Microsoft

When This Method Works Best

• Clean, structured data with consistent formatting
• Simple duplicate detection across entire rows
• Small to medium datasets (under 100,000 rows)
• One-time cleanup tasks

Intermediate Conditional Duplicates with Formulas

Sometimes you need more control over which duplicates to keep:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1

This formula identifies duplicates based on multiple columns, allowing you to:

• Flag duplicates without removing them
• Apply different rules for different types of duplicates
• Keep the most recent entry instead of the first

Intermediate Intermediate: Using Pivot Tables for Duplicate Analysis

Before removing duplicates, smart analysts understand their data:

1. Create a PivotTable from your data
2. Add suspect fields to Rows
3. Add a count field to Values
4. Filter for counts > 1

This reveals patterns: Are duplicates random errors or systematic issues?

Advanced Advanced Filtering for Complex Scenarios

When you need surgical precision:

1. Data → Advanced Filter
2. Check “Unique records only”
3. Set criteria range for conditional logic
4. Copy results to new location

This method preserves your original data while creating a clean copy.

See our guide about duplicates in your data