By default, a Pivot Table will count all records in a data set. To show a unique or distinct count in a pivot table, you must add data to the object model when the pivot table is created. In the example shown, the pivot table displays how many unique colors are sold in each state.

Fields

The pivot table shown is based on two fields: State and Color. The State field is configured as a row field, and the Color field is a value field, as seen below.

Pivot table unique count field configuration - 1

In the Pivot Table, the Color field has been renamed “Colors”, and “Summarize values by” has been set to “Distinct count”:

Pivot table unique count value field settings - 2

Data model

When the Pivot Table is created, the “Add this data to the Data Model” box is checked. This is what makes the distinct count option available.

Pivot table unique count add to data model - 3

Steps

  1. Create a pivot table , and tick “Add data to data model”
  2. Add State field to the rows area (optional)
  3. Add Color field to the Values area
  4. Set “Summarize values by” > “Distinct count”
  5. Rename Count field if desired

Notes

  1. Distinct count is available in Excel 2013 and later

In this example, a pivot table is used to show the year-over-year change in sales across 4 categories. Change can be displayed as the numeric difference (this example) or as a percentage.

Fields

The pivot table uses all three fields in the source data: Date, Sales, and Color:

Pivot table year over year comparison - 4

The Color field has been added as a Row field to group data by color. The Date field has been added as a Column field and grouped by year:

Date grouped by year only - 5

The Sales field has been added to the Values field twice. The first instance is a simple Sum of Sales:

Pivot table year over year comparison sales field settings - 6

The second instance of Sales has been renamed “Count”, and set to show a “Difference from” value, based on the previous year:

Change field settings - 7

Note: Column H values are empty since there is no previous year. It has been hidden for cosmetic reasons only.

Steps

  1. Create a pivot table
  2. Add Color field to Rows area
  3. Add Date field to Columns area, group by Year
  4. Add Sales to Values as Sum
  5. Add Sales to Values, rename to “Change” Show values as = Difference From Base field = Date (or Year) Base item = Previous
  6. Hide first Change column (optional)

Notes

  1. To show percentage change, set Show values as to “% Difference From” in step 5 above.
  2. If Date is grouped by more than one component (i.e. year and month) field names will appear differently in the pivot table field list. The important thing is to group by year and use that grouping as the base field.
  3. As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year . Then add the Year field to the pivot table directly.