Excel Pivot Table Count Unique
One of the key elements of Pivot Table is it takes all the rows into consideration and shows the count of the data as those many rows but in case of knowing unique count we don’t have a default option. So, would you like to know how many customers are there instead of how many transactions?? Then this article will take you through of counting unique values in pivot tables.
What is Unique Value Mean?
Assume you have gone to one of the supermarkets 5 times to buy groceries in the last month, now Supermarket owner wants to know how many customers have come in the last month. So here the question is they want to know how many customers have come not how many times, so in your case, you might have gone 5 times but actual customer count is 1 only not 5.
For example, look at the below data.
In the above data table, we have two customers “John” & “Peter”, they went to the supermarket at different dates, but when we apply the pivot table to get the total customer count we will get the result like the below one.
Upon applying the pivot table we got the unique customer count as 7 whereas only two customers have come to the supermarket. So getting a unique count isn’t the straight forward one.
This is what is all about the unique count, but with Pivot Table we don’t have this luxury of getting unique values straight away, so we need to use a different strategy to get the unique count of values.
How to Get Unique Value in Pivot Table?
Note: You need to have Excel 2013 or later versions to use this technique.
Take the above-used data only for this example as well.
Choose the data range from A1 to D8 to insert a pivot table.
After selecting the data range to insert a pivot table, go to the INSERT tab in excel and click on the “Pivot Table” option.
If you hate manual steps then simply you can press the shortcut in excel of ALT + N + V, upon pressing the above shortcut key it will open up below the “Create Pivot Table” window for us.
In the above window choose the pivot table destination as “Existing Worksheet”.
After choosing the worksheet as this Existing Worksheet we need to choose the cell address where we need to insert the pivot table.
One more important thing we need to make in the above window is we need to make sure the checkbox of “Add this data to the Data Model” is ticked.
Click on “Ok” and we will have a blank pivot table in place.
Insert the “Item” field to the “ROWS” area and “Customer” field to the “VALUES” area.
Now we are getting the same total as the previous one but we need to change the calculation type from COUNT to “DISTINCT COUNT”.
Right-click on of customer count cell and choose “Value Field Setting”.
This will open up below window for us and in this, we can see actually what is the current summarization type is.
As we can see above current summarization type is “COUNT”, so pivot table showing the current count of customers as 7 because there are 7 line items in the selected data range of pivot table.
This is the default summarization that will be chosen in the case of “TEXT” values used in the “VALUES” area of the pivot table.
In the same above window scroll down to the bottom and choose the summarization type as “DISTINCT COUNT”.
After selecting the summarization type as “DISTINCT COUNT” click on “Ok” and we will have a unique count of customers as 2 instead of 7.
So, we have an accurate count of customers in place. Actually we can see the column header as “Distinct Count of Customer” instead of the previous header of “Count of Customer”.
Like this using Pivot Table’s “Distinct Count” summarization type we can actually get the unique count of values.
Things to Remember
- We need to choose the check the box of “Add this data to the Data Model” while inserting a pivot table.
- DISTINCT COUNT summarization type is available from Excel 2013 onwards only.
Recommended Articles
This has been a guide to Pivot Table Count Unique. Here we discuss how to count unique values in an excel pivot table with some examples. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
