Excel - How to find duplicates
This tutorial explains how to manage duplicate entries on the different version of Excel (2003, 2007 or 2010):
Using the Remove Duplicates tool
Select the cells range, go to the "Data"
tab and click "Remove Duplicates"
A dialog box will then appear, tick the "My data has headers"
checkbox if required and click on "OK
" to confirm.
All duplicate entries will be deleted!
Using the advanced filter
- Excel 2003: Menu> Data> Filter and select "Advanced Filter"
- Excel 2007 and 2010: Click on "Data" > "Advanced":
In the dialog box that appears, select:
- Copy to another location
- Cell range
- Select Unique record only
- Click "OK".
- You can also use this method to permanently remove duplicates rather than copying the unique values to a new worksheet. To do this, select "Filter the list, in-place" instead of "Copy to another location"
Using a PivotTable to find the number of duplicate entries
- Add to the right of your list, a column filled with 1s.
- NB: you have to give a title to your columns (e.g "list" and "count").
- Select both columns and go to Insert > Insert a PivotTable
- In the menu that opens on your right, tick the list and count checkboxes
- Select the first line of the Count column
- Click on Sort > Largest to Smallest
- The number of duplicate entries will be listed from the largest to smallest.