How to delete duplicates on excel

Answer

To delete duplicates in Excel, select the target data range, use the built-in “Remove Duplicates” tool, and confirm which columns define a duplicate. Excel removes the repeated entries and keeps one instance of each unique record.

Understanding Duplicate Removal

Excel identifies a duplicate when all selected columns match across two or more rows. Excel removes the extra rows and retains the first instance.

How to Delete Duplicates Using the Remove Duplicates Tool

Windows and Mac instructions

1. Select the range containing the records.

2. Open the Data tab.

3. Choose Remove Duplicates.

4. Select the columns that define a duplicate entry.

5. Confirm by selecting OK.

6. Review the confirmation message showing how many duplicates Excel removed.

The tool location is the same on Windows and Mac. The shortcut paths match on both systems.

How to Delete Duplicates for a Specific Column Only

1. Select the specific column.

2. Open the Data tab.

3. Select Remove Duplicates.

4. Confirm that the selected column is checked.

5. Choose OK.

Excel removes rows where that single column matches existing values.

How to Highlight Duplicates Before Deleting Them

Conditional formatting exposes repeated entries before removal.

1. Select the target range.

2. Open the Home tab.

3. Choose Conditional Formatting.

4. Select Highlight Cells Rules.

5. Choose Duplicate Values.

6. Confirm your highlight color.

This method enables review before deletion.

How to Delete Duplicates Without Losing the Original Data

Creating a backup range preserves the source.

1. Copy the dataset to a new sheet.

2. Select the copied range.

3. Choose Remove Duplicates from the Data tab.

4. Confirm the columns to evaluate.

5. Select OK.

This process safeguards the source data for later reference.

How to Delete Duplicates Using Advanced Filters

Advanced Filters extract unique records into a new location.

1. Select the source range.

2. Open the Data tab.

3. Select Advanced in the Sort & Filter group.

4. Select Copy to another location.

5. Check Unique records only.

6. Set the destination cell.

7. Choose OK.

Excel copies the unique list without modifying the source.

Why Excel May Not Delete Duplicates

Excel does not remove duplicates when extra spaces, invisible characters, or mismatched formats exist. A mismatch prevents Excel from marking records as identical.

Common issues

1. Leading or trailing spaces.

2. Hidden characters from pasted data.

3. Mixed number and text formats.

4. Merged cells in the data range.

Data cleaning increases successful detection rates.

How to Clean Data Before Removing Duplicates

Windows and Mac steps

1. Use TRIM, CLEAN, or VALUE functions in helper columns to standardize content.

2. Convert all text-formatted numbers to true numbers through Value or Text to Columns.

3. Remove spaces at the start or end of text fields.

4. Replace irregular characters through Find & Replace.

Clean data enables more accurate duplicate detection.

How to Delete Duplicates While Keeping the Last Entry

Excel keeps the first instance by default. Sorting rearranges the dataset to keep the last row instead.

1. Sort the dataset using the target column.

2. Choose descending order.

3. Select Remove Duplicates.

4. Confirm the evaluation columns.

5. Select OK.

Excel now retains the last instance because sorting changed the row order before removal.

How to Delete Duplicates Based on Multiple Columns

1. Highlight the dataset.

2. Open the Data tab.

3. Select Remove Duplicates.

4. Check the specific columns that define unique records.

5. Select OK.

Excel uses combined column values to detect duplicate rows.

Best Practices for Managing Duplicates

1. Clean data before using Remove Duplicates.

2. Highlight duplicates before deleting.

3. Create a backup sheet.

4. Use advanced filters for unique extraction.

5. Use sorting to control which instance Excel keeps.

6. Use helper columns for data cleanup when dealing with imports.

7. Document the chosen duplicate criteria when sharing files.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report