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
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:
https://www.myonlinetraininghub.com/excel-pivottables-unique-count-3-ways
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.
Please Share
If you liked this please click the buttons below to share.
Alex says
Hi Mynda,
Thank you for this post.
I believe the case when your data has BLANK cells deserves special attention. What surprised me is that the blank cells are also counted. For example, when your column has {1, 2, (blank)} entries DISTINCTCOUNT returns 3, while COUNT—just 2. I wish Microsoft made a provision to exclude blank cells in distinct counting. So, beware of traps.
Mynda Treacy says
Hi Alex,
Distinct Count counts the different types of values in a field. If you have blank cells then this is also a type of value, albeit a blank, and therefore it is included in the distinct count.
Mynda
Gulraiz A. Qureshi says
I think Pivot Tables are not for me. I receive quotations from 2-3 different suppliers around the world with sometimes hundreds of items included. All items have a different description, different part number, different substitute part number, quantity (some time same), different Price and different totals. How can I benefit from Pivot Tables.
Furthermore, I receive price of different flight checks from different companies, or price for various trainings from different companies. I tried Pivot Tables but no results to my usage.
Can you advise how can I benefit from Pivot Tables?
Mynda Treacy says
Hi Gulraiz,
It depends what you want to achieve. PivotTables may not be the right tool for the job.
If you have data in multiple tables with different codes, descriptions etc. for the same items, then that is a data problem. You need to create some form of mapping table that maps the different codes etc. from each supplier into one common code so you can then compare apples with apples.
You would have to build that mapping table yourself. There’s no automated tool to do that for you.
Mynda