How to Remove Duplicates in Excel (5 Methods for Clean Data)

Removing duplicates in Excel is essential for keeping your spreadsheets accurate, especially when working with large datasets. In this guide, we cover five proven methods to find and remove duplicates efficiently—from quick tools to more advanced Excel features.

Quick Summary

Method	Best For	Permanent Removal?	Excel Version
Remove Duplicates Tool	Simple duplicate rows	Yes	All modern versions
Conditional Formatting	Visual detection	No	All versions
Advanced Filter	Copying unique values	No	All versions
Formulas (COUNTIF, CONCATENATE)	Custom conditions	No	All versions
Power Query	Large, dynamic data	Yes	Excel 2016+ / 365

Method 1: Use the “Remove Duplicates” Tool

This built-in feature lets you delete duplicate values in seconds.

Steps:

1. Select your data range.
2. Go to the Data tab.
3. Click Remove Duplicates.
4. Choose columns to check.
5. Click OK.

Tip: Always back up your data before using this method as it deletes values permanently.

Method 2: Highlight Duplicates Using Conditional Formatting

This method lets you see duplicates without deleting them.

Steps:

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

Great for review and manual cleanup.

Method 3: Filter Unique Values with the Advanced Filter

This lets you create a separate list of unique values.

Steps:

1. Select your data.
2. Go to Data > Sort & Filter > Advanced.
3. Choose “Copy to another location”.
4. Check Unique records only.
5. Set destination and click OK.

Ideal for exporting or preserving original data.

Method 4: Use Formulas (COUNTIF + CONCATENATE)

Custom formulas give you full control over what is considered a duplicate.

Example Setup:

• Combine columns: =A2&B2&C2
• Check duplicates: =COUNTIF($D$2:$D$100,D2)

Steps:

1. Add helper columns for combined values and count.
2. Filter results where count > 1.
3. Delete or copy unique records.

Works great when duplicate criteria span multiple columns.

Method 5: Use Power Query (Best for Large or Ongoing Data)

Power Query lets you automate duplicate removal.

Steps:

1. Select data > Go to Data > Get & Transform > From Table/Range.
2. In Power Query Editor, select column(s).
3. Right-click and choose Remove Duplicates.
4. Click Close & Load to send cleaned data to worksheet.

Best for recurring reports or real-time data cleaning.