Exceljet

Quick, clean, and to the point

Pivot table show duplicates

Pivot tables make it easy to count values in a data set. One way  this feature can be used is to display duplicates. In the example shown, a pivot table is used to show duplicate cities in an Excel Table that contains more than 250 rows.

Fields

The data contains 263 rows, each with a City and Country.  The pivot table shown is based just one field: City, which has been added as both a Row field and a Value field:

Pivot table show duplicates - field list

In the Values area, the City field has been renamed "Count" and set to summarize by count :

Pivot table show duplicates -  value field settings

In the Rows area, the City field is filtered to show only cties where  count is greater than 1:

Pivot table show duplicates -  city filter

In addition, the City field is set to sort by count in descending order:

Pivot table show duplicates -  city sort

Steps

  1. Create a pivot table
  2. Add City field to the rows area
  3. Add City field to theValues area
    1. Summarize by count
    2. Rename "Count"
    3. Filter on Cities where count > 1
    4. Sort in descending order by count

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.