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:
- 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:
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.
- 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.
- The following is an example of the third suggestion: Sum of Units for each item.
- The output here is the total number of units sold for each item.
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.
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.
- 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.
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.
- So now, I have added the Units parameter to the Values option.
- So output will be the number of units sold by individuals for different items.
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.
- The output is that the Values in the PIVOT table are displayed as a contribution of % of the grand total, as displayed below:
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!
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.
- Output is table without the value of items less than £50.
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.
- 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.
- 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.
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.