How to Remove Duplicates in Excel
Learn how to remove duplicates in Excel, choose columns, protect original data, use conditional formatting, troubleshoot mistakes, and read FAQs.
Quick Answer
To remove duplicates in Excel, save a copy, select your data range, choose Data > Remove Duplicates, pick the columns Excel should compare, confirm, and review the remaining rows.
What You Need
- Excel workbook
- Data table
- Column headers
- Backup copy
- Clear rule for what counts as duplicate
Safety Precautions
- Save a backup before deleting rows.
- Choose comparison columns carefully.
- Check whether headers are selected.
- Use conditional formatting first if you only want to review duplicates.
- Do not remove duplicates from filtered data without checking the full range.
Step-by-Step Instructions
- Step 1
Save a copy
Protect the original workbook before changing rows.
- Step 2
Select the range
Click inside the table or highlight the exact data.
- Step 3
Open Remove Duplicates
Go to Data > Remove Duplicates.
- Step 4
Confirm headers
Check My data has headers if your range includes headers.
- Step 5
Choose columns
Select the columns that define a duplicate.
- Step 6
Run the tool
Click OK and note how many rows Excel removed.
- Step 7
Review results
Spot-check the remaining data before saving over the original.
Practical Example
Example: You remove duplicate email subscribers by comparing only the Email column, not the name column.
Common Mistakes
- Not backing up
- Selecting only one column accidentally
- Comparing the wrong columns
- Ignoring headers
- Removing rows before reviewing
- Confusing unique values with duplicate rows
Troubleshooting
Duplicates remain
Check spaces, capitalization, and hidden characters.
Wrong rows were removed
Undo immediately and choose comparison columns again.
Excel counts values as different
Clean data with TRIM or standard formatting first.
Need to see duplicates first
Use conditional formatting to highlight duplicates.
Helpful Internal Links
FAQs
Does Remove Duplicates delete rows?
Yes, it removes duplicate rows from the selected range.
Can I review duplicates before deleting?
Yes, use conditional formatting or a helper column first.
Which columns should I select?
Select the columns that define a true duplicate for your task.
Can I undo Remove Duplicates?
Yes, immediately use Undo if the workbook is still open.
Related Guides
How to Create a Pivot Table in Excel
Learn how to create a pivot table in Excel, prepare data, choose fields, summarize values, refresh results, avoid mistakes, and read FAQs.
How to Add Drop Down List in Excel
Learn how to add a drop down list in Excel using Data Validation, source lists, error alerts, common mistakes, troubleshooting, and FAQs.
How to Apply Conditional Formatting in Excel
Highlight values, duplicates, dates or formula-based conditions in Excel and manage rule order and cell references.
Help us improve this guide
Tell us about an outdated instruction, unclear step, broken link or safety concern. Do not include passwords, account numbers, medical records or other sensitive information.