How to count unique items (distinct count) in an Excel pivot table. Four methods, for different versions of Excel. Get the free workbook to follow along
If you're using a pivot table to summarize your data, you might have questions such as:
There isn't a built-in "Unique Count" feature in a normal pivot table, but in this tutorial, you'll see how to get a distinct count of items in a pivot table.
This video shows the steps to show a distinct count with the Excel Data Model.
Often we have large data sets that we want to analyse with the aid of a Pivot Table, but we only want to count the unique occurrences of some items of data. Unfortunately, Pivot Tables have never had the inbuilt facility to count unique values.
In this example, the sample file has 4999 records that show product sales, with the region and salesperson name. The first few records are shown in the screen shot below. You can download the sample file at the link below.
Creating a Pivot Table from the data would give us something like the following, if we added Person to the Values area of the Pivot table and summarised it with Count.
However, this is giving us a count of all the transactions, not the count of the unique number of people who made those transactions within each Region.
There are workarounds that you can use, to get a unique count:
All four methods are described below, along with a performance comparison between methods 2 and 3.
In Excel 2013, if you add a pivot table's source data to the workbook's Data Model, it is easy to create a unique count.
NOTE: This technique creates an OLAP-based pivot table, which has some limitations, such as no grouping, and no calculated fields or calculated items. If you need these restricted features, try the "pivot a pivot" method instead.
To create the pivot table, try the following steps:
Next, to set up the pivot table layout, follow these steps:
Finally, to get a unique count of people in each region, follow these steps:
The Value Field Settings window opens, where:
To change the calculation type:
In the pivot table, the Person field changes automatically.
Now, instead of showing the total count of transactions, it shows a distinct count of salespeople's names, for each region.
To get a unique count of salespeople per region in Excel 2010, we can produce a Pivot Report, and then "Pivot the Pivot" to get our final result. Thanks to Roger Govier, who created this tutorial and workbook.
This now looks like a normal table of data, but it is a summary of the original full data table with 4,999 rows.
Next, you'll name the first cell in the pivot table.
The next step is to calculate the location of the last row in the pivot table. We'll use the MATCH function, with a very large number, to find the last number in column D, and get its row number. This formula will only work in a column with numbers.
Next, we will create a dynamic Named Range, to refer to this new table. This range will start in the cell named pvtstart (the Region heading cell), and end in the last row of data in column E (the named range, PvtEnd).
Finally, create a second Pivot Table, based upon the dynamic range, myData.
Put Region in the Row area, and Person, Units and Value in the Values area
This shows the number of unique salespeople per regions as shown below. For example, in the first pivot table, the East region showed 8 unique names in the person column, and that is the count in this pivot table.
Naturally, as there are 2 Pivot Tables involved in this solution, both have to be refreshed after any data has been added or changed in the source table. It is essential that the first PT is refreshed first, followed by a Refresh of the Final Pivot Table.
You can refresh the pivot tables manually, or use amacro. To use a macro, add one of the following procedures to the sheet with your final Pivot Table.
A) If both pivot tables are on the same sheet, use this code:
Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub
B) If the pivot tables are on different sheets then use this code (substitute the name of your actual sheet for "yoursheetname"):
Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub
If you are using macros to refresh the pivot tables, you could also use a macro to reset the myData named range after each refresh. Add the following procedure to your workbook, then call this SetmyData procedure in the Worksheet_Activate procedures.
Sub SetmyData() 'Change "PT_A" to whatever name 'you have given to your first Pivot Table 'that is to be used as the source 'for for the final Pivot Table 'Also change the sheet name 'from "Method_1" to 'your sheet name if it is different Dim PTName As String Dim ShName As String PTName = "PT_A" ShName = "Method_1" ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1 End Sub
In earlier versions of Excel, pivot tables don't have the option of repeating row labels.
In those versions, to get a unique count of salespeople per region, you can add a new column in the source data, and enter a COUNTIF formula
You can download the "No Data Model" sample file in the download section, below.
Follow these steps, to add the formula:
In the source data, add a new column heading -- "Unique" -- in cell J1
In cell J2, enter this formula, and copy it down to the last row of data:
The first reference in the range ($E$5) is Absolute, and the second is relative (E5), so as we copy the formula down the page, so the range will expand from $E$5:E5 to $E$5:E6, then $E$5:E7 and so on.
In each row we are checking how many times the value in column E (Person) has occurred, down to the current row.
With the extra column added, the data would look like this. The second instance of "Harry" returns a zero in the Unique column.
NOTE: If there are people of the same name selling in different Regions:
To see the unique counts in a pivot table:
We can see that there are 30 people who made the Sales, and the number of each who are in each Region, and if we chose to expand any Region then we would see the individual totals for each Person.
If possible, use the "pivot a pivot" method, which is much faster.
Using a COUNTIF formula in the source data works, and gives us our desired result, and on a relative small dataset the method is acceptable. But, it is very costly in terms of processing time, and on very large datasets can be extremely slow.
Using Charles Williams' "FastExcel" to calculate the time it takes for the workbook to calculate gives the result shown below, where the time taken to recalculate the Data sheet is 224 milliseconds.
The "Pivot a Pivot" method also produced the correct result, with a unique count of Person, as required. In addition, it does not rely upon additional columns in the source data, nor entering of any new formulae.
The resulting workbook is smaller, and again using Charles Williams' "FastExcel" to calculate the time for calculation of the Data sheet is an incredibly fast 1.2 milliseconds
With small datasets timing of recalculation may not be critical, but when dealing with larger datasets of 300,000 to 400,000 rows, then these differences would become very material.
If you have the PowerPivot add-in installed, you can use it to show a distinct count for a field. This video shows the steps for creating a Power Pivot pivot table, and adding a field with the unique count.
Sample File: To follow along with the video, download the sample file that was used for this video
Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.
Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier
You can contact Roger at: [email protected]
Last updated: December 15, 2021 10:55 AM