Pivot tables are excellent tools for counting and summing data, but you might struggle to get a unique or distinct count, because this feature is hidden in a normal pivot table. 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. 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 a 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 to 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 see a count of records 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 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. When I make color a value field, and show as a 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
