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

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

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - How to find duplicates », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).