Ever wanted to count distinct values in your pivot tables? Something like this:
Here is a simple trick to add distinct count to Excel pivot tables easily.
Let’s say you have data like this:
As you can see, several products are repeated on each day. When you make a pivot table from this data and add product count, Excel counts all products. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once). To get distinct count in the pivot table,
These instructions work only in Excel 2016, Office 365 and Excel 2013.
- Insert a pivot table from your data
- In the create pivot dialog, enable “Add this data to data model” option.
- Once you have the pivot table canvas, add the product (or any other field for which you want to calculate distinct count) to the values area.
- Right click on the values, go to “Value field settings”.
- Summarize the value by “Distinct count”. This is the last option.
- All done!
Distinct Count in Excel Pivot Tables – Example Workbook
If you want to practice this or want to see this with an example, here is the workbook.