Pivot Table Count Unique

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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.

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.

Pivot table Count unique values Example 1

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.read more to get the total customer count, we will get the result like the one below.

Pivot table Count unique (Count)

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

You can download this Pivot Table Count Unique Excel Template here – Pivot Table Count Unique Excel Template

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.

Pivot table Count unique values Example 1

Choose the data range from A1 to D8 to insert a PivotTable.

Pivot table Count unique values Example 1-1

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.read more and click on the “PivotTable” option.

Pivot Table Insert

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.read more of ALT + N + V.Upon pressing the above shortcut key; it will open up below the “Create PivotTable” window for us.

Pivot table Count unique values Example 1-2

In the above window, choose the PivotTable destination as “Existing Worksheet.”

Pivot table Count unique values Example 1-3

After choosing the worksheet as this Existing Worksheet, we need to select the cell address where we need to insert the PivotTable.

Pivot table Count unique values Example 1-4

Another important thing we need to do in the above window is that the “Add this data to the Data Model” checkbox is ticked.

Example 1-5 (data model)

Click on “OK,” and we will have a blank PivotTable.

Example 1-6

Insert the “Item” field to the “ROWS” area and the “Customer” field to the “VALUES” area.

Example 1-7 (Field values)

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

Example 1-8 (Value field settings)

It will open up the below window for us. In this, we can see what the current summarization type is.

Example 1-9 (Count)

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

Pivot table Count unique values Example 10

After selecting the summarization type as “DISTINCT COUNT,” click on “OK.”  We will have a unique count of customers as 2 instead of 7.

Pivot table Count unique values Example 11

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.read more 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: –