In this video, we’ll look at how to get a unique count in a pivot table.
Note: requires Excel 2013+ on Windows
Pivot tables are excellent tools for counting and summing data, but you might struggle to get a unique or discount count, because this feature is hidden in a normal pivot table.
Let me illustrate with this set of data.
Notice we have a date, state, color, quantity, and a total. I’ll insert a pivot table to summarize the data.
Now, to see how many states we have in the data, I can add State as a Row field.
Pivot tables are very good at giving you a list of unique values.
You can see here that there are 12 states total in the data.
Let’s see if I can display that number in the pivot.
If I add state as a value, we do see a count. But this count represents the number of rows or records in the data for each state, not the number of states.
If I check field settings, under Summarize values by, we see nothing about unique or distinct counts.
In order to get this in a pivot table, we need use Excel’s Data Model.
Let me create this pivot again. This time, in the Create Pivot Table window, I’ll check “add this pivot table to the data model”.
The data model taps into a huge range of additional functionality that I won’t get into now. The key point here is it adds a new field setting for distinct count.
Now, I’ll add state again as a Row field, and Value field.
As before, we get a count of rows for each state.
But this time, when I check field settings, at the bottom of the list, we have an option for distinct count.
And this gives us what we want. If I make color a row field, we’ll see the number of states in the data associated with each color.
I’ll clear the pivot table and run through the same process with color.
When I add color as a Row field, we see 9 colors listed. This is the total number of colors in the data. When I add color as a value field, and and show as distinct count, the pivot table displays 9.
Now I can use this count as I like.
For example, I can show the number of unique colors sold in each state, along with total sales.
Like all pivot tables, this is a dynamic report. If I edit the data and add a new color, then refresh, we see the count change.
If I add a new state, the pivot expands as needed.