Excel - How to find duplicates

December 2016


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

  • The number of duplicate entries will be listed from the largest to smallest.

Related :

This document entitled « Excel - How to find duplicates » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.