|
Home > Pivot > Calculation > Count Unique How to Count Unique Values in a Pivot Table
|

Video: Pivot Table Unique Count
Show Distinct Count in 3 Steps
-- Step 1) Create Pivot Table with Data Model
-- Step 2) Add Fields to Pivot Table
-- Step 3) Get the Distinct 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:
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 CountIn 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. |
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.
First, to create the pivot table, follow these steps:

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

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

The Value Field Settings window opens, where:
Finally, to change the calculation type, follow these steps:

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

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

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:

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.
Here are two more ways to get the number of unique values in an Excel pivot table.
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.
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

Be sure to get my monthly newsletter, with quick Excel tips, links, news, and a bit of fun. Just add your email, then click Sign Up.
Calculated Items vs Calculated Fields
Last updated: February 16, 2026 12:05 PM