See all How-To Articles

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.

 

uniquevalues unique table

 

Count Distinct Values in a Pivot Table

Consider the following table of data.

 

uniquevalues data

 

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

 

uniquevalues data mode

 

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

 

uniquevalues new pivot

 

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

 

uniquevalues fields

 

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

 

uniquevalues field settings

 

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

 

uniquevalues distinct count

 

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.

countpivot raw 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.

 

pivot unique gs table

 

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

 

pivot unique gs count unique

 

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

 

pivot unique result

 

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

See all How-To Articles