The following step-by-step example shows how to create a pivot table in Google Sheets that displays the count of unique values of some variable.
Step 1: Enter the Data
First, let’s enter the following data that shows the total revenue generated by certain products in certain regions for some company:
Step 2: Create the Pivot Table
To create a pivot table that summarizes this dataset, click the Insert tab and then click Pivot table:
In the window that appears, type in the range of the data to use for the pivot table and select a cell in the existing sheet to place the pivot table:
Once you click Create, an empty pivot table will automatically be inserted.
In the Pivot table editor that appears on the right side of the screen, click Add next to Rows and choose Region.
Then click Add next to Values and click Product.
By default, Google Sheets will choose COUNTA as the way to summarize the Product values:
To instead display the unique count of products by region, click the dropdown arrow under Summarize by and click COUNTUNIQUE:
The pivot table will update to show the unique count of products by region:
For example, we can see:
- There were 3 unique products in the East region.
- There were 3 unique products in the North region.
- There were 3 unique products in the South region.
- There were 3 unique products in the West region.
And so on.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
Google Sheets: How to Format Pivot Tables
Google Sheets: Display Percentage of Total in Pivot Table
Google Sheets: How to Create Pivot Table from Multiple Sheets