Exceljet

Quick, clean, and to the point

Pivot table unique count

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

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

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

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

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.