Count Distinct Values in an Excel 2013 PivotTable
This post demonstrates how to count the number of distinct (unique) values in an Excel 2013 PivotTable. Prior to Excel 2013, this capability was not built-in to the PivotTable feature. For Excel versions earlier than 2013, there are a variety of different workarounds available, some use VBA code, some use helper formulas, and some of them use functions such as COUNTIF, COUNTIFS, and SUMPRODUCT. The good news is that beginning with Excel 2013, this capability is built-in to the standard PivotTable feature. This post demonstrates how to perform a distinct count with an Excel 2013 PivotTable.
First, let’s review our objective. We have some sales transactions with region, item, and amount columns, as shown below.
Our objective is to create a PivotTable that counts the number of unique region/item combinations. For example, we want the PivotTable to show that for region N, the count of unique items is 2.
Traditional PivotTable Limitation
With Excel 2010 and earlier, the best we could do is to count the number of transactions (rows) with any given region/item combination. This is illustrated in the screenshot below.
The count illustrated above is not what we want. We do not want to count the number of transactions/rows, we want to count the unique, or distinct, number of region/item combinations. We want a PivotTable that reports 1 for E since there is one unique region/item combination for E (X101). We expect 2 for N, since there are two items for N (X101, X102). We expect 4 for S, since there are four items (X101, X102, X103, X104). We expect 3 for W (Y200, Y201, Y202).
Excel 2013 Distinct Count PivotTable
To obtain a distinct count in an Excel 2013 PivotTable, here is what we do. We select any cell in the data source, as normal, and then click Insert > PivotTable, as normal. However, we need to add this data source to the Data Model. This is easily performed by checking the relevant check box in the Create PivotTable dialog box, as seen below. Please note, this “Add this data to the Data Model” checkbox is a new option with Excel 2013.
Next, we build the PivotTable as we normally would by inserting the region and item fields into the Rows area. Next, we insert the item field into the Values area. Now, this is the good part. We open the Values Field Settings dialog box for the item field we placed into the Values area, and look…a brand new option! The Distinct Count choice in the Summarize value field by list box, as shown below, is new in Excel 2013.
Clicking OK results in the glorious PivotTable pictured below.
As you can see, our desired counts are all here, E is 1, N is 2, S is 4 and W is 3.
If it is not important to see the item labels, we can remove the item field from the Rows area, or, hide the detail; either way is fine. This will result in the PivotTable below.
So, that is a great new PivotTable enhancement rolled out in Excel 2013. Thanks Microsoft!
Excel file used in the above screenshots: PTDistinctCount