Excel - How to find duplicates

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 Destination

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