Duplicate data is the enemy of accurate analysis. Whether you are merging customer lists or cleaning up inventory logs, knowing how to de-dupe your spreadsheet is an essential skill.
This guide covers the three most effective methods, ranging from the built-in button to dynamic formulas.
Removing duplicates deletes data permanently. Before you start, copy your original data to a new sheet just in case.
Method 1: The "Remove Duplicates" Button
This is the fastest method for static lists. It deletes duplicates instantly.
- Select the range of cells that contains the duplicates.
- Go to the Data tab in the Ribbon.
- Click Remove Duplicates (in the Data Tools group).
- Ensure "My data has headers" is checked if applicable.
- Click OK. Excel will tell you how many duplicates were removed.
Method 2: The UNIQUE Function (Dynamic)
If you have Excel 365 or Google Sheets, this is the modern way. It extracts a list of unique values to a new location, leaving your original data untouched.
- A2:A100 is your source data.
- The result will automatically "spill" into the cells below.
- Advantage: If your original data changes, the unique list updates automatically.
Method 3: Conditional Formatting (Highlight Only)
Sometimes you don't want to delete them yet; you just want to see them.
- Select your data range.
- Go to Home > Conditional Formatting.
- Select Highlight Cells Rules > Duplicate Values.
- Choose a color (e.g., Light Red Fill) and click OK.
Need a complex formula?
Trying to remove duplicates based on multiple criteria or specific logic? Standard tools might not work.
Describe your specific data problem to our AI, and it will generate the exact formula (or VBA code) you need.