In a slightly departure from my normal posting habits, I wanted to share a quick hack to solve something that has been bothering me in Excel for ages. Finally, with Excel 2013, I’ve discovered there is a way to determine the distinct count of unique items in a list using pivot tables, something that wasn’t previously possible without a fair bit of work. Numerous Google searches on the topic didn’t reveal much (aside from some older forums and a number of Stack Overflow threads) so I thought I’d share here in case anyone else has had similar frustrations.
If you have Excel 2013, then select your data to create a pivot table, and when you create your table, make sure the option ‘Add this data to the Data Model’ tickbox is check (see below).
Tick the box next to ‘Add this data to the Data Model
Then, when your pivot table opens, create your rows, columns and values normally. Then click the field you want to calculate the distinct count of and edit the Field Value Settings:
Edit field value settings
Finally, scroll down to the very last option and choose ‘Distinct Count.’
Choose the option ‘Distinct Count
This should update your pivot table values to show the data as a distinct count of unique values. Job done!