Distinct count in Excel pivot tables

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. 

1. Insert a pivot table from your data
2. In the create pivot dialog, enable “Add this data to data model” option.
3. 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.
4. Right click on the values, go to “Value field settings”.
5. Summarize the value by “Distinct count”. This is the last option.
6. 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.

More Pivot Table Tricks

• Making pivot report from multiple tables
• Rename pivot table headings
• Rearrange row labels in pivots
• Generate combinations of two tables with pivots
• More on Excel Pivot Tables