Let me illustrate with this set of data.
Notice we have date, state, color, quantity, and a total.
I’ll insert a pivot table to summarize the data.
Pivot tables are very good at giving you a list of unique values.
you’re free to see here that there are 12 states total in the data.
Let’s see if I can display that number in a pivot.
If I add state as a value, we do see a count.
If I check field parameters, under Summarize values by, we see nothing about unique or distinct counts.
so that get this in a pivot table, we need to use Excel’s Data Model.
Let me create this pivot again.
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 see a count of records for each state.
And this gives us what we want.
I’ll clear out 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.
Now I can use this count as I like.
Like all pivot tables, this report is dynamic.
If I edit the data and add a new color, then refresh, we see the count change.
And if I add a new state, the pivot table expands as needed.
