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 the 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 the 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 on 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 a 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 excelThe INSERT Tab In ExcelIn excel “INSERT” tab plays an important role in analyzing the data. Like all the other tabs in the ribbon INSERT tab offers its own features and tools. Under Insert Tab we have several other groups including tables, illustration, add-ins, charts, Power map, sparklines, filters, etc.read more 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 the “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 the 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 the pivot table showing the current count of customers as 7 because there are 7 line items in the selected data range of the 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 valuesUnique Count Of ValuesIn Excel, there are two ways to count values: 1) using the Sum and Countif function, and 2) using the SUMPRODUCT and Countif function.read more.
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
45.6K subscribers