This is because you didn’t add the source data of the pivot table to the data model. Checking the box “Add this data to the data model” enables you to turn on distinct count feature in Excel pivot table.
Distinct count is a great function in Pivot table. However, sometimes Excel doesn’t show the “Distinct count” option. Sometimes, when you forget to add the data to the data model, you cannot see Distinct Count in Pivot Table calculation options.
In this article, I will show you how to enable distinct count in pivot table step by step.
You may also be interested in How to Generate Random Dates in Excel
Why is distinct counting not showing in pivot table
There are two reasons why you don’t have “Distinct count” as an option in Excel Pivot Table.
- You didn’t add your source data to the data model
- You are using Excel version older than Excel 2013
Distinct count is a feature that is only shown when you add the source data to the data model. If you didn’t add the data to the data model when you created this pivot table, the option will not appear.
This feature is an in-built feature for Excel 2013 and version after that.
How to enable distinct count in pivot table?
In this article, I am going to use the below table as an example.
Step 1: Select the range
Step 2: Select “PivotTable”
Step 3: Check the “Add this data to the Data Model” box
Step 4: Press “OK”
Step 5: Right Click column header
Step 6: Select “Value Field Settings”
Step 7: Select “Distinct Count”
Step 8: Press “OK”
You may also be interested in How to copy data from multiple sheets at once
Do you find this article helpful? Subscribe to our newsletter to get regular Excel tips and exclusive free Excel resources.
