When you create a standard Pivot Table from a dataset containing duplicate values, the count summary will, by default, include all values, including duplicates.
For instance, in the dataset shown in the screenshot below, although there are only three unique order numbers (1110, 1111, and 1112) in column B, the Pivot Table on the right counts all six order numbers, including duplicates.
However, sometimes you may be only interested in getting the count of distinct values in a Pivot Table as shown in the screenshot below.
The distinct count of order numbers will help you answer questions such as, ‘How many orders were placed by each department?’
If you right-click any value in a normal Pivot Table, you will find that the ‘Summarize Values By Distinct Count’ option is unavailable and you cannot use it to get distinct values in the normal Pivot Table.
In this tutorial, I will show you two ways to count distinct values in an Excel Pivot Table. You can use the first method if you have Excel 2013 and later and the second method if you have an Excel version earlier than 2013.
Method #1: Count Distinct Values in a Pivot Table Using the Summarize Values By Distinct Count Option
If you have Excel 2013 and later, you can count distinct values in a Pivot Table using the Summarize Values By Distinct Count option.
When you create a Pivot Table, the Distinct Count option will be unavailable unless you add the data to the Data Model.
When you add the data to the Data Model, the Distinct Count option becomes accessible, allowing you to count unique values in the Pivot Table.
Suppose you have the dataset below with duplicate order numbers in column B.
You want to create a Pivot Table from the dataset and show the count of distinct order numbers.
Here’s how to do it:
- Select any cell in the dataset.
- Click the Insert tab, open the Pivot Table drop-down, and select the From Table/Range option.
The step above opens the ‘PivotTable from Table or Range’ dialog box, with the target dataset’s reference already populated in the Table/Range field.
- On the ‘PivotTable from table or range’ dialog box, choose where you want the Pivot Table to be placed, select the ‘Add this data to the Data Model’ checkbox, and click OK.
The above step places an empty Pivot Table in the destination worksheet and displays the Pivot Table Fields task pane on the right of the Excel window.
- On the Pivot Table Fields task pane, drag the Department field to the Rows area and the Order Number field to the Values area.
The above step populates the Pivot Table with the sum of order numbers. The Order Number field defaults to ‘Sum of Order Number’ because it contains numeric values.
- Right-click any value on the Pivot Table, hover over the ‘Summarize Values By’ option, and select ‘Distinct Count’ on the submenu.
The above step changes the Pivot Table to display the count of unique order numbers, as shown in the screenshot below.
Also read: How to Change Data Source in Pivot Table
Method #2: Count Distinct Values in a Pivot Table Using a Helper Column
The ‘Summarize Values By Distinct Count’ option is unavailable in Excel versions earlier than 2013.
If you have an older version of Excel you can use a helper column to pre-process the data before creating a Pivot Table to display the count of distinct values.
Suppose you have the below dataset showing orders placed by various departments.
You want to create a Pivot Table to show the count of distinct order numbers.
Here’s how to do it:
- Add a helper column to the dataset and enter the formula below.
=IF(COUNTIF(B$2:B2, B2)=1, 1, 0)
The formula returns the value 1 for the first occurrence of each distinct order number and 0 for duplicates.
- Select any cell in the dataset.
- Click the Insert tab, open the Pivot Table drop-down, and select the From Table/Range option.
The above step opens the ‘PivotTable from Table or Range’ dialog box, with the target dataset’s reference already filled in the Table/Range field.
- On the ‘PivotTable from table or range’ dialog box, choose where you want the Pivot Table to be placed, and click OK.
Note: I am using a newer version of Excel, so your dialog box may appear differently from mine.
The above step places an empty Pivot Table in the destination worksheet and displays the Pivot Table Fields task pane on the right of the Excel window.
- On the Pivot Table Fields task pane, drag the Department field to the Rows area and the Helper Column field to the Values area.
The step above updates the Pivot Table with the sum of the values in the helper column, effectively counting the distinct values in the Pivot Table.
I have shown you ways to count distinct values in an Excel Pivot Table. I hope you found the tutorial helpful.
Other Excel articles you may also like:
