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 quickly.
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.
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
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Beautiful Budget vs. Actual chart to make your boss love you||Excel formula showing as text instead of actual result – How to fix the problem »|