How to Use Pivot Tables in Google Sheets

How to Use Pivot Tables in Google Sheets
Reading Time: 6 minutes

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:
Creating PIVOT Table
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:
Inserting PIVOT Table into a new sheet
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.
Google Sheets suggestions for analysis
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.
Average of Unit Cost for each item
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.
Using Google Sheet suggestion for analysis
Figure 5: Using Google Sheet suggestion for analysis
  • The output here is the total number of units sold for each item.
Sum of units of 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.
Manually adding parameters to Pivot Table
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.
Adding parameters to Rows and Values
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.
Displaying values as Averages instead of Sum
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.
Adding parameters to Columns
Figure 10: Adding parameters to Columns
  • So now, I have added the Units parameter to the Values option.
Adding values parameter to Values option
Figure 11: Adding values parameter to Values option

  • So output will be the number of units sold by individuals for different items.
Number of items sold by individuals for different categories of 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. 
Sorting the PIVOT Table 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:
Sorting the values by % of grand total
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!
Adding filters to Pivot Table
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.
Filtering values by selecting items less than 50 pounds
Figure 16: Filtering values by selecting items less than 50 pounds
  • Output is table without the value of items less than £50.
Items sold for less than 50 pounds are displayed
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.
Filter by Condition
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.
Applying specific condition for filtering values in Pivot Table
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.
Output is items sold by individuals greater 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.