There are several ways you can remove duplicates in your data from a spreadsheet in Excel.
Remove Duplicate Data
Time needed: 2 minutes
To remove duplicates in Excel,
- With your spreadsheet open, click into (or select) your data
- Click on the Data tab
- In the Data Tools group, choose Remove Duplicates
- When the dialogue box open, make sure you select whether or not your data has headers
- Choose which columns you want Excel to look for duplicate data in (it may be all of them)
- Click OK
- Excel will tell you how many duplicate records were removed, and how many unique records remain
- Click OK
Remove Duplicates in Data Using Filters
With your spreadsheet open, click on the Data Tab, and in the Sort & Filter group, click on Advanced.
The Advanced Filter dialogue box will open. Choose if you want to copy your filtered list to a new location, or filter in place.
If you filter in place, make sure to tick Unique Records only.
Then click OK
The filtered list will have blue row numbers
In the bottom left-hand corner you will see how many records are unique.
If you copy to another location, click on the arrow next to Copy To
Click on the cell where you want your new list to start
Click on the blue down arrow to restore your dialogue box.
Tick Unique records only.
Click OK. You new list of unique records will appear.