Excel Pivot Table Count Unique
One of the key elements of the PivotTable is that it considers all the rows and shows the data count as those many rows. But in the case of knowing the unique count, we do not 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 counting unique values in PivotTables.
Table of contents
What is Unique Value Mean?
Assume you have gone to one of the supermarkets 5 times to buy groceries in the last month. The supermarket owner wants to know how many customers have come in the previous month. So, the question is, they want to know how many customers have come, not how many times. So, you might have gone 5 times in your case, but the actual customer count is 1, not 5.
For example, look at the below data.
In the above data table, we have two customers, “John” and “Peter,” who went to the supermarket on different dates. Still, when we apply the pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. to get the total customer count, we will get the result like the one below.
Upon applying the PivotTable, we got a unique customer count of 7. In comparison, only two customers have come to the supermarket. So, getting a unique count is not straightforward.
It is all about the unique count, but with PivotTable, we do not 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 PivotTable.
After selecting the data range to insert a PivotTable, 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. and click on the “PivotTable” option.
If you hate manual steps, you can press the shortcut in excelShortcut In ExcelAn Excel shortcut is a technique of performing a manual task in a quicker way. of ALT + N + V.Upon pressing the above shortcut key; it will open up below the “Create PivotTable” window for us.
In the above window, choose the PivotTable destination as “Existing Worksheet.”
After choosing the worksheet as this Existing Worksheet, we need to select the cell address where we need to insert the PivotTable.
Another important thing we need to do in the above window is that the “Add this data to the Data Model” checkbox is ticked.
Click on “OK,” and we will have a blank PivotTable.
Insert the “Item” field to the “ROWS” area and the “Customer” field to the “VALUES” area.
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 the customer count cell and choose “Value Field Setting.”
It will open up the below window for us. In this, we can see what the current summarization type is.
As we can see above, the current summarization type is “COUNT.” Hence, the PivotTable shows the current count of customers as 7 because there are 7 line items in the selected data range of the PivotTable.
The default summarization will be chosen in the case of “TEXT” values used in the “VALUES” area of the PivotTable.
In the 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.” We will have a unique count of customers as 2 instead of 7.
So, we have an accurate count of customers in place. We can see the column header as “Distinct Count of Customer” instead of the previous header of “Count of Customer.”
Like this, we can 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. using PivotTable’s “Distinct Count” summarization type.
Things to Remember
- We must check the “Add this data to the Data Model” box while inserting a PivotTable.
- The “DISTINCT COUNT” summarization type is available from Excel 2013 onwards only.
This article is a guide to PivotTable Count Unique. Here, we discuss how to count unique values in an Excel PivotTable with some examples. You may learn more about Excel from the following articles: –