Pivot Table – Count Unique Values in Excel and Google Sheets

This tutorial demonstrates how to count unique values with a pivot table in Excel and Google Sheets.

 

 

Count Distinct Values in a Pivot Table

Consider the following table of data.

 

 

1. In the Ribbon, go to Insert > PivotTable.

 

 

2. Make sure that Add this data to the Data Model is checked, and then click OK.

 

 

3. Now drag the Product field down to the Rows area, and the Salesperson field down to the Values area.

 

 

4. In the drop down next to the count of the Salesperson field, choose Value Field Settings.

 

 

5. From the Summarize value field by list, choose Distinct Count, and then click OK.

 

 

The data is changed to show how many salespeople sold a particular product.

Count Unique Values in Google Sheets Pivot Table

Consider the following table of data.

We can create a Pivot table in Google Sheets, adding the Region as a Row Header and the Salesperson as a value.   As the SalesPerson is not a numeric figure, it will automatically add a COUNTA summary for that field.

 

 

In the Summarize by drop down, change the function from COUNTA to COUNTUNIQUE.

 

 

Now the data only shows how many salespeople are in each specific region.

 

 

Tip: Try using some shortcuts when you’re working with pivot tables.