Excel - How to find duplicates

September 2017


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

Published by jak58. Latest update on January 28, 2015 at 04:20 AM by jak58.
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).