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 on just one field: City, which has been added as both a Row field and a Value field:

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

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

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

Steps

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