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?
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.
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.
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.
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:
These are not the only alternatives; click here to learn more about the wide range of aggregation functions for Pivot Tables.
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.
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.
We hope this article has helped you and given you a better understanding of how to create a pivot table in Google Sheets.
You might also like our articles on how to automatically number rows in Google Sheets and how to refresh pivot table in Google Sheets. On a side note, we also recommend reading checking out our guide on how to copy our free address book template for Google Sheets to optimize your workflow better and trying our renewals management software.
