How to Use Pivot Tables in Google Sheets

Reading Time: 6 minutes

Table of Contents

What are Pivot Tables in Google Sheets?

Pivot Tables help you summarise the data stored in the table. Pivot Table is a drag and drop analysis tool, in which you can select the rows, columns, values and filters according to what you want to analyse. You can interchange rows with columns and vice versa. You can analyse patterns within data by using Pivot tables and find useful insights.

How to Create Pivot Tables in Google Sheets

To create a pivot table, select the Insert tab from the toolbar.
Select Pivot table from the options provided, as shown below:

Figure 1: Creating PIVOT Table

You can create a pivot table on the sheet you are working on, or on a different sheet.
So here, I have created it on a different sheet.
This is because of the ease of understanding and for a better view, as shown below:

Figure 2: Inserting PIVOT Table into a new sheet

Google Sheet Recommendations for analysis in Pivot Table

Google Sheets recommends some analysis based on the data in your pivot table.
So you can use these suggestions to analyse your data.
You can see the parameters chosen by the Google Sheets recommendations on the side panel for better understanding.

Figure 3: Google Sheets suggestions for analysis

As you can see in the above image, Google Sheets has provided three suggestions.
The following is an example of the first suggestion: Average Unit Cost per Item.
The output here is the average selling price of individual items.

Figure 4: Average of Unit Cost for each item as an example of pivot tables in Google Sheets

The following is an example of the third suggestion: Sum of Units for each item.

Figure 5: Using Google Sheet suggestion for analysis

The output here is the total number of units sold for each item.

Figure 6: Sum of units of each item as an example of pivot tables in Google Sheets

Manually Adding Parameters to Pivot Table

In the previous section, you learnt about how you can use the suggestions provided by Google Sheets for analysing data.
In this section, you will learn how to manually add parameters to Rows, Columns, Values or Filters for doing specific analysis of data.

Figure 7: Manually adding parameters to Pivot Table

Adding Parameters to Rows and Values

In this example, you will add the Name and Units parameter in the Rows option
Now, add the Total parameter in the Values option.
The result is a total sales figure for individuals of different units.

Figure 8: Adding parameters to Rows and Values

As you can see in the figure, you can change the order of input parameters to Ascending or Descending order, as per convenience.
Here I have displayed the total sales as a sum of all items sold by an individual.

Displaying Values As Average of Values

In the previous example, you had displayed values as a sum of all items sold by an individual.
Here’s an example of an individual’s total revenue displayed as an average price of everything they sold.

Figure 9: Displaying values as Averages instead of Sum

Adding Parameters to Rows, Columns and Values

You haven’t added Columns in the examples learnt till now.
Here, I have added Items in the Columns option.
This will create different columns for different items.

Figure 10: Adding parameters to Columns

So now, I have added the Units parameter to the Values option.

Figure 11: Adding values parameter to Values option

So output will be the number of units sold by individuals for different items.

Figure 12: Number of items sold by individuals for different categories of items as an example of pivot tables in Google Sheets

Sorting the PIVOT table by Percentage of Grand Total

Till now, you have either the total number of units sold or the average number of units sold by individuals.
If you want to analyse the percentage contribution of different items sold by individuals to the grand total, Google Sheets provides an option for that.
Sort the Units in Values options by % of grand total.

Figure 13: Sorting the PIVOT Table by % of grand total

The output is that the Values in the PIVOT table are displayed as a contribution of % of the grand total, as displayed below:

Figure 14: Sorting the values by % of grand total

Adding Filters to Pivot Table

Till now, all the data is displayed in the pivot table, based on the parameters selected.
Let’s say, you want to see only those values which are higher or lower than a certain number, or want to manually enter a filter, Google Sheets provides a provision for that too!

Figure 15: Adding filters to Pivot Table

1. Filter by Values

You can filter the pivot table by the values displayed.
Here, I want to see what items are sold which are priced below £50.
Hence, I have manually selected values lower than £50 in the Filters option.

Figure 16: Filtering values by selecting items less than 50 pounds

Output is table without the value of items less than £50.

Figure 17: Items sold for less than 50 pounds are displayed

2. Filter by Condition

If you want to manually enter a condition to filter the pivot table, choose the Filter by Condition option in the Filter drop-down menu.

Figure 18: Filter by Condition

Select the expanded menu and choose the condition category you want to use.
You can also write a custom formula, which is displayed in the last row of the expanded menu, as displayed in the image below.
Here I have selected greater than option.

Figure 19: Applying specific condition for filtering values in Pivot Table

I want to filter the values where the selling price is greater than 500 pounds.
Hence, I entered £500 in the next box.
Output is the number of items sold by individuals whose price is higher than 500 pounds.

Figure 20: Output is items sold by individuals greater than 500 pounds.

See Also

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

Hlookup in Google Sheets: learn about Hlookup in google sheets and try some examples.