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:

Pivot table show duplicates - field list - 1

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

Pivot table show duplicates - value field settings - 2

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

Pivot table show duplicates - city filter - 3

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

Pivot table show duplicates - city sort - 4

Steps

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

You can use a pivot table to display the top or bottom values in a set of data. In the example shown, one pivot table is used to show the top 3 scores in a set of data, and another pivot table is used to show the bottom 3 values in the same set of data. Because one pivot table was cloned from another, they share the same pivot cache, and both will update when either pivot is refreshed.

How to make this pivot table

  1. Create a new pivot table on the same worksheet
  2. Add the Name field to the Rows area
  3. Add the Score field to the Values area
  4. Rename the Score field from “Sum of Score” to “Score " (note trailing space)
  5. Filter values to show “Top 3 items by Score”
  6. Set sort to “Descending by Score”
  7. Copy entire pivot table and paste at cell I4
  8. Filter values to show “Bottom 3 items by Score”
  9. Set sort to “Ascending by Score”
  10. Disable grand totals if desired