Pivot Table Count Unique

PivotTable is known for many factors and one of the key elements 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?

This article will take you through counting unique values in PivotTables.

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.

Let us learn how to find unique value in PivotTable in Excel with the following detailed examples.

Example #1

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 PivotTable 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.

Note: You need to have Excel 2013 or later versions to use this technique.

Example #2

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 Excel and click on the "PivotTable" option.

If you hate manual steps, you can press the shortcut in Excel 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 values using PivotTable's "Distinct Count" summarization type.

Example #3

We can easily find the PivotTable count unique with simple steps.

First, choose the data range from to insert a PivotTable.

After selecting the data range to insert a PivotTable, go to the INSERT tab in Excel and click on the "PivotTable" option.

In the window, choose the PivotTable destination as "Existing Worksheet and then, select the cell address where we need to insert the PivotTable.

Next, click 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 will get 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 window with current summarization type.

With various summarization values, we can find required unique values.

Likewise, we can use PivotTable Count Unique.

• 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.