.png)
How to Create a Pivot Table in Google Sheets
Pivot Tables in Google Sheets can be an incredibly useful tool for analyzing data. This article explain everything you need to know to effectively use Pivot Tables in Google Sheets.
A powerful feature of Google Sheets is the Pivot Table. It can be used to condense a large dataset into a small set of useful values for your data analysis. The Pivot Table can be daunting at first, but we have prepared this article and a set of tutorials for you to maximize the power of Pivot Tables. Are you ready?
What are Pivot Tables?
Google Sheets Pivot Tables are essentially Google Sheets’ answer to Excel’s PivotTable feature. The Pivot Table is designed to summarize a large amount of data stored in a small array. While by default this involves summing the columns, you can also add custom functions to find what you need for your analysis.
What makes it advantageous? To be fair, you can replicate a Pivot Table using the existing functions of Google Sheets. However, that would be extremely time-consuming if you have a large amount of data that can easily reach hundreds of rows and possibly dozens of columns! It would be quite a hassle to compile a table manually. The Pivot Table comes in as the solution for that! You will discover the functionality it has while reading this article.
How to Create a Pivot Table in Google Sheets
Inserting a Pivot Table is a straightforward process.
Step 1: Click on any cell in the array of data, then click Insert on the main menu, then click Pivot table on the drop-down list that will appear.
Step 2: A box will appear with some settings for your Pivot Table, including the range of data and whether you want to create a new sheet for it or not. By default, Google Sheets prefers that you create the Pivot Table in a new sheet. Click Create.
Step 3: The Pivot table generated is initially empty.
To add content, look at the Pivot table editor sidebar that appears on the right side of Google Sheets. First, we need to specify the rows. Click the Add button besides the Rows label. A drop-down list of columns from the source array will appear. Select the column label appropriate to your purpose. For our case, we will use the Items column.
What Google Sheets does here is to scan all the values under the item column, list the unique values, and then aggregate all the rows by each unique value. The Pivot Table now looks as follows:
Step 4: We can now specify the values to aggregate in the Pivot Table. By default, the sum of the values under a certain column is calculated and shown. Click the Add button besides the Values label. The list of column labels will appear again. Select the column label appropriate to your purpose. For our case, it’s the profit column.
The result is:
You can repeat Step 4 to add more columns as you need.
In the next section, we will explore further the capabilities of Pivot Table.
Check the individual entries grouped under a row
If you need to check the individual entries grouped under a row, you can simply double-click on the specific cell. A new sheet will appear, listing all the entries grouped under it.
Capabilities of Google Sheets’ Pivot Tables
Google Sheets’ Pivot Table is not just for summarizing the values of a column. You can do so much more! Here are some of the features:
- Summarizing values by date and time. One of the most common criteria to summarize entries is to summarize by date and time. You will need to do a few tricks to make it work. Check our tutorial to learn more about it.
- Alternatives for summarizing values instead of SUM. By default, the values are aggregated into SUM; however, this formula is not the only option. You can aggregate it and get a different value. Some of the other formulas are listed below:
- COUNT - counts the number of entries
- AVERAGE - calculates the average value of the entries
- MAX - the maximum value
- MIN - the minimum value
- STDEV - the standard deviation of the set of values
These are not the only alternatives; click here to learn more about the wide range of aggregation functions for Pivot Tables.
- Add a custom formula to the Pivot Table. Sometimes the source sheet does not have a column for the data you need, and you cannot modify the source sheet. If the built-in functions listed above are not enough, you can add a custom formula on the Pivot Table. Check our tutorial on adding a custom formula to the Pivot Table.
- Sort the row labels. You can sort the row labels in either ascending or descending order. A drop-down box for it will appear for appropriate columns, click it to choose between Ascending or Descending order. Click here to learn more about sorting entries in the Pivot Table.
- Format the Pivot Table. You can also format a Pivot Table to improve its look and change how it displays the information. Learn more by reading our tutorial on formatting Pivot Tables in Google Sheets.
- Consolidate data from several sheets. If your data stretches over several sheets but you need to consolidate them into a single Pivot Table, you can use the QUERY function to combine them. Check out this tutorial to know how to use the QUERY function alongside the Pivot Table.
How to use GETPIVOTDATA to access data stored in a Pivot Table
A useful function you can use to further process the data in the Pivot Tables is the GETPIVOTDATA. Instead of requiring you to pinpoint the exact cell where the data is stored inside the Pivot Table (it is possible), the GETPIVOTDATA helps you find the data given the attributes you are familiar with, the column and row headers you have specified in the source sheet. You can learn more by accessing our tutorial on using GETPIVOTDATA here.
How to delete a Pivot Table
If you no longer need a Pivot Table, you can opt to delete it by clicking the upper-leftmost cell. There are other ways of deleting a Pivot Table that you can use as well.
