ExcelInsider Team
The values of a pivot table can do different types of calculations, but if you need the number of distinct counts, that option is not usually visible. Imagine you have students from different sections getting different grades in their subjects. The student’s names will usually be repeated for each subject. To get the actual count of the students, you will need the distinct count.
However, if the pivot table does not provide the option to select distinct counts, it would be difficult to perform the calculation. In this article, we will learn how to fix the pivot table’s distinct count missing.
➤ Go to the source data, and create a new pivot table from Insert > PivotTable.
➤ Check the box that says “Add this data to the Data Model” and press OK.
➤ In the new pivot table, the value field will have a distinct count option.
Pivot tables need data models to calculate the distinct count. If you don’t add the pivot table to a data model, the distinct count option will not be available.
Visualization of the Distinct Count Missing Error
We have a pivot table here with some property listings. There are property IDs, the cities of the properties, the types of the properties, the price, and the names of the people who listed the properties for sale. Although the properties are different, there aren’t many variations in the property types. To determine the number of distinct types of property, we need to find the distinct count of these types.
➤ Here, we have the Type field in the Values area. Click on that and select Value Field Settings.
➤ A new window pops up. The Count option is already selected, but there is no Distinct Count option.
Solution 1: Add a New Pivot Table to the Data Model
The distinct count option is something that only works when the pivot table is added to a data model. If it is not in the data model, the distinct count option is not provided by Microsoft Excel.
➤ We need to go to the original data source. We can find the data source by going to the PivotTable Analyze tab in the ribbon and selecting Change Data Source from the Data group.
➤ Now we can see what the pivot table source is. Close the window and go to the source to create a new pivot table.
➤ Select the data range, and go to Insert > PivotTable.
➤ We don’t need to change many options, but we must check the “Add this data to the Data Model” box.
➤ Press OK to create the new pivot table.
➤ In the new pivot table, move Property ID, City, and Listed By to the Rows area, and Price ($) and Type to the Values area.
➤ Now, click on Count of Type and select Value Field Settings.
➤ The Distinct Count option is available now. Select that and press OK.
➤ The pivot table now shows the distinct count properly.
Solution 2: Add the Existing Pivot Table to the Data Model
Creating a new pivot table may not be viable for every case. If you have already designed the pivot table according to your needs, you don’t want to do that all over again. Fortunately, it is possible to create a new pivot table with the data model without changing anything.
➤ In the PivotTable Fields section, select More Tables…
➤ A new window will open asking for confirmation to create a new pivot table. Press Yes to copy the exact pivot table to another sheet, but this time with a data model.
➤ Now in the new pivot table, you can change the Count of Type to Distinct Count of Type.
Solution 3: Upgrade Microsoft Excel
In Microsoft Excel 2010 and older, the data model option is not available. As a result, you cannot select Distinct Count no matter what. Buy a newer version of Microsoft Excel and open the Excel sheet in that version, then try Solution 1 or 2. If you are using Microsoft Excel on a Mac, you won’t have the Distinct Count option either. You can run Windows in a virtual machine and install Microsoft Excel on that, or buy a Windows computer to get the distinct count in a pivot table.
Frequently Asked Questions
How to refresh the pivot table with a distinct count?
The method is the same as refreshing a regular pivot table. Right-click on the pivot table and select Refresh to refresh the pivot table. You can also press Alt + F5 to refresh the pivot table, or go to the PivotTable Analyze tab and select Refresh. Make sure the pivot table is selected before applying any of these methods.
How to add a distinct count in a pivot table?
Move the required field to the Values section, and click on it. Select Value Field Settings to change the type of value. Now, select Distinct Count from the new window and press OK to add the distinct count to the pivot table.
How to count distinct values in Excel?
You can use the following formula to do so:
=SUM(1/COUNTIF(A1:A10, A1:A10))
Replace A1:A10 with the range of the data. The number of distinct counts will show up.
Why is my PivotTable Analyze tab missing?
Because you haven’t selected the pivot table. Select any cell of the pivot table to make the PivotTable Analyze tab show up on the ribbon.
How to add a count in a PivotTable?
If you move a non-numeric field to the Values section, it automatically converts to a Count value. To add a count for a numeric field, click on that field in the Values area and go to Value Field Settings. Then, select Count, and press OK to convert that field to a count.
Wrapping Up
Distinct Count is helpful in a pivot table to sort out the unique values of a field. If the option is not available, it can be really frustrating to analyze the data properly. We have learned three methods on how to get the distinct count back to the pivot table. Each method has its value, and you should learn all of them to get a clear view of the issue.
