With the integration of Power Pivot into Excel 2013 (known as the Data Model), we now have access to a range of more advanced ‘Power’ tools right from within Excel.
One of these tools enables us to easily get the distinct count for a field in a PivotTable.
Note: Don’t worry if you don’t have Excel 2013 or 2016, or don’t have the Data Model (Power Pivot) in your version of Excel, I’ll show you a workaround you can use instead.
Download the workbook
Enter your email address below to download the sample workbook.
Excel PivotTable Distinct Count Example
Let’s take the data below and say we want to know how many different products we have in each category, in other words, the distinct count of Products per Category.
The end result will look like this:
Step 1: Insert a PivotTable and in the ‘Create PivotTable’ dialog box check the ‘Add this to the Data Model’ box:
This adds the data to Power Pivot /the Data Model.
Note: If you don’t have this check box available it means your version of Excel doesn’t include Power Pivot, but don’t worry, remember I’m going to show you a workaround.
Step 2: Build the PivotTable placing the Product field (i.e. the field you want to count) in the Values area. This will return the count of the records/transactions for the products.
Then, to display the Distinct Count right-click the values column > Value Field Settings > Summarize Values By > Distinct Count:
Warning: If you have blank cells in the field you're counting they will also be included in the distinct count:
It makes sense since blank cells are also a distinct item in your list, but I can understand that it's not always desireable to include them.
If you want to exclude blanks from your distinct count then you can either remove them from your source data, or write a Measure/Calculated Field using Power Pivot's DAX functions, like so:
=CALCULATE( DISTINCTCOUNT(Table1[Product]), NOT( ISBLANK(Table1[Product])))
This formula gets entered in the Power Pivot tab > Calculated Fields (Excel 2013) or Measures (Excel 2010 & 2016) dialog box.
It's then available in the PivotTable Fields List:
Thanks to Alex Klimson for raising this issue.
Don’t have the Data Model
If you don’t have the ‘Add this data to the Data Model’ check box available in your version of Excel, then you have two options depending on whether you’re using Excel 2007 or Excel 2010 as described in this post:
Want More Power
Power Pivot, or the Data Model (why it has two names is beyond me), has a load more cool features which enable you to go beyond the limitations of regular PivotTables.
To get a better understanding of what Power Pivot can do for you, or if you’d like to learn Power Pivot, please take a moment to check out my Power Pivot course.
Tip: Don’t attempt Power Pivot unless you are already proficient with PivotTables. Click here to check out our PivotTable course.
If you liked this please click the buttons below to share.