Home > Pivot > Calculation > Count Unique

Count Unique Items in Pivot Table

Debra Dalgleish - Contextures

In an Excel pivot table, you can show a count of unique items. For example, how many unique sales reps are in the East region? My video shows the steps, and there are written steps too, and a sample file to download. (for Excel 2013 and later versions).

Distinct Count of person names

What is a Unique Count?

In an Excel pivot table, you might want to count the unique occurrences of some items of data. For example, if you're analyzing sales data, you might have questions such as:

  • How many unique (distinct/different) customers made purchases in each region?
  • How many unique products were sold in each store?
  • How many unique salespeople sold each product per region?

There isn't a built-in "Unique Count" feature in a normal pivot table, but in this tutorial, you'll see how to get a distinct count of items in a pivot table.

Video: Pivot Table Unique Count

In Excel 2013, and later versions, if you add a pivot table's source data to the workbook's Data Model, it's easy to create a unique count.

In this video, I show how to calculate a distinct count with the Excel Data Model, and there are written steps below the video.

Show Distinct Count in 3 Steps

There are 3 main steps to show a distinct count in an Excel pivot table, and the detailed steps are shown below.

-- Step 1) Create Pivot Table with Data Model

-- Step 2) Add Fields to Pivot Table

-- Step 3) Get the Distinct Count

These steps work in Excel 2013 and later versions.

Step 1) Create Pivot Table with Data Model

First, to create the pivot table, follow these steps:

  • Select a cell in the source data table.
  • At the bottom of the Create PivotTable dialog box, add a check mark to "Add this data to the Data Model" -- DO NOT SKIP THIS STEP
  • Click the OK button, to create the blank pivot table

add to the data model

Step 2) Add Fields to Pivot Table

Next, to set up the pivot table layout, follow these steps:

  • In the pivot table, add Region to the Row area.
  • Add these 3 fields to the Values area -- Person, Units, Value
  • Person field contains text, so it defaults to Count of Person.
    • The count shows the total number of transactions in each region
    • It does not show a unique count of people

count of transactions not unique count

Step 3) Get the Distinct Count

Finally, to get a unique count of people in each region, follow these steps:

  • Right-click one of the values in the Person field
  • In the pop-up menu, click on Value Field Settings

select Value Field Settings

Value Field Settings

The Value Field Settings window opens, where:

  • the first tab, Summarize Values By, is showing
  • there is a list of calculation types
  • Count is selected, because the field is currently using that type of calculation

Change the Calculation Type

Finally, to change the calculation type, follow these steps:

  • In the Summarize Value Field By list, scroll to the bottom of the list
  • Click on the Distinct Count calculation type
  • Click the OK button, to close the dialog box.

select Distinct Count

Completed Pivot Table - Unique Count

After you click OK button, the Person field in the pivot table changes automatically.

Now, instead of showing the total count of transactions, it shows a distinct count of salespeople's names, for each region. It does not count duplicate values

Distinct Count of person names

Data Model Limitation

To use the Distinct Count function, the pivot table's source data must be added to the Data Model.

add to the data model

OLAP-Based Pivot Table

When you click that Data Model check box, Excel creates an OLAP-based pivot table, which has limitations, compared to a normal Excel pivot table.

For example, the following features are disabled in an OLAP-based pivot table:

features not available in OLAP-based pivot table

More about OLAP

On the Microsoft website, there are more details on OLAP (Online Analytical Processing) features and limitations.

If you dan't want an OLAP-based pivot table, try one of the other ways to get a unique count, shown in the next section.

Other Ways to Get Unique Count

Here are two more ways to get the number of unique values in an Excel pivot table.

  1. In Excel 2010 and later versions, use a 2 pivot table technique. The second pivot table is based on the first one.
  2. In Excel 2007 and older versions of Excel, add a column to the source data, and use the COUNTIF function. This method can cause the workbook to calculate slowly.

Get the Sample File

Unique Count: Download the Data Model Unique Count sample workbook, which has the data and pivot table. The file is zipped, and in xlsx format. The file does not contain macros.

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier

You can contact Roger at: roger@technology4u.co.uk

Roger Govier Roger Govier

Technology 4 U

Related Tutorials

Unique Count - No Data Model

Calculated Items vs Calculated Fields

Calculated Item Examples

Calculated Field - Count

 

 

Last updated: March 29, 2024 10:02 AM