How To Create A Pivot Table In Google Sheets
When working with large datasets in Google Sheets, sometimes you’ll want to view only portions of your data based on certain criteria, or to group your data in specific ways to get new insights.
Fortunately, Google Sheets comes with a tool that can do just that.
Pivot tables are a built-in tool of Google Sheets that helps you easily summarize data, find patterns, and reorganize information
Adding a Pivot Table
Once you have the data you want to analyze in Google Sheets, here’s how to add a pivot table:
Select the columns of data you want to create the pivot table from. Be sure your data columns have headers
Open the Data menu and click on the Pivot Table option
The Create Pivot Table dialog will appear with the data range already filled in.
Leave it set to Insert to: New Sheet to insert your pivot table in a new sheet and click Create
The Pivot Table Editor sidebar will appear.
This is where you’ll decide what data to display in the pivot table. Google Sheets will automatically provide some suggestions.
For example, in the screenshot below, one suggestion Google provides is to display the average age
You can click on any of the suggestions to quickly add that suggestion to the pivot table
Customizing Pivot tables
In addition to adding the suggested fields to your pivot table, you can also create your own custom calculation fields.
For example, say we want to add a column that calculates the maximum age for each city to our pivot table, here’s how to do that:
In the Pivot Table Editor sidebar, find the Values heading and click the Add button beside it, then select Calculated Field from the menu that appears
A new grey box will appear with the label Calculated Field 1 at the top.
Choose the Custom option under the Summarize menu, then type the following formula into the Formula field to calculate the maximum age in each row: “=MAX(AGE)”
A new column will be inserted in the pivot table displaying the maximum age for each row.
In this case the data is grouped into rows by city, so the new column will display the maximum age in each city
To update the header for your new column, simply double click on the column header and type in the new header
Now any time you update the data in the original data range, the pivot table will update to display the summarized data
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to create a pivot table in Google Sheets. Want more? Check out all the Google Sheets Tutorials.