Learn how to use Excel's built-in 'Remove Duplicates' feature to clean your data while ensuring your original information remains safe and intact. This guide provides a safe, step-by-step process.
Before making any changes, it's crucial to back up your data. Right-click on your sheet tab, select Move or Copy, check the Create a copy box, and click OK. Work on the copied sheet to prevent accidental data loss.
Click any cell within the data set you want to clean. Excel will automatically detect the entire range. Alternatively, you can manually select the specific columns and rows.
Go to the Data tab in the Excel ribbon at the top of the window.
In the Data Tools group, find and click the Remove Duplicates button.
A dialog box will appear listing all the columns in your selected range. Check the boxes for the columns you want Excel to use to identify duplicate rows. For a row to be a duplicate, the data in all selected columns must match.
Click OK. Excel will remove the duplicate rows and display a message telling you how many duplicate values were found and removed, and how many unique values remain.
Pro Tips for Managing Duplicates
Instead of deleting immediately, use Conditional Formatting (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) to see duplicates before you decide to remove them.
Carefully select which columns define a duplicate. Checking only a 'First Name' column could incorrectly remove people with the same name but different details in other columns.
This is the most important tip. The 'Remove Duplicates' action cannot be easily undone after the file is saved and closed. A backup ensures your original data is always safe.
For a non-destructive alternative, use the Advanced Filter tool (Data > Advanced). It can copy unique records to a new location, leaving your original data set untouched.
Explore other Excel guides and shortcuts