Excel - How to find duplicates

Ask a question

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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team