Spreadsheets offer powerful analysis capabilities, but sometimes it feels like they're missing that extra layer of insight. When there's a massive amount of data, it's tough to summarize or draw conclusions from a basic spreadsheet view.
That's where pivot tables come in. Most Excel power users use pivot tables as their bread and butter. But you can also use pivot tables in Google Sheets.
Here, I'll walk you through how to build pivot tables in Google Sheets. To follow along, copy our demo spreadsheet, and then play around with it as you work through the tutorial.
Debating between Microsoft Excel and Google Sheets? Check out our app showdown to find out which is right for you: Google Sheets vs. Excel.
What is a pivot table in Google Sheets?
A pivot table takes a large set of data, usually in a spreadsheet, and summarizes it.
If you're working with a small spreadsheet, you likely don't need a pivot table. That's because small data sets are usually simple enough to scan through to make sense of the information. But as your spreadsheet begins to grow, drawing conclusions requires a bit more power. That's where pivot tables come in.
Think of it this way. Normal spreadsheets essentially have "flat data" represented by two axes: horizontal (columns) and vertical (rows).
In the example below, each sale is shown as its own row, and each column offers different information about that sale.
If you want to derive more insights, you'll need to add data on another level. To do this, you need to shift (or pivot) the axes of the table. As shown in the example below, now you're no longer looking at your data by individual sale. Instead, you're looking at aggregated data: how many units did each sales rep sell for each product?
So that's the rough idea: you take a two-dimensional table and pivot it to introduce a third dimension.
While you could pull many of these insights using formulas, the pivot table allows you to distill it in a fraction of the time—and with less chance for human error. This way, whenever your boss asks for a new report based on the same data set, you can generate it with a few clicks instead of starting from scratch.
How to use a pivot table in Google Sheets
Here's a quick look at how to use a Google Sheets pivot table. (Keep scrolling to learn more specifics and practice with our demo spreadsheet.)
Open a Google Sheets spreadsheet.
Select all of the cells with source data that you want to use (including column headers).
Click Insert, and select Pivot table.
In the Create pivot table panel that appears, choose if you want to insert your pivot table into a new sheet or an existing sheet. Then click Create.
In the Pivot table editor panel, next to Rows and Columns, click Add for each one. This lets you select the data you want to analyze.
In the same panel, next to Values, click Add. This lets you select the values you want to display within the rows and columns.
If you want to display only values that meet certain criteria, add filters.
The data in your pivot table will automatically change if the source data changes. If you don't see the changes reflected in your pivot table, refresh your page. It may take a minute to update, depending on the volume of data changes.
How to create a pivot table in Google Sheets
Google Sheets pivot tables are as easy to create as they are powerful. Here's more detail on how to create and use a pivot table in Google Sheets.
Select all of the cells with source data that you want to use, including the column headers. To highlight all your data at once, use the keyboard shortcut:
command+Aon a Mac orctrl+Ain Windows. (If your data set contains columns without headers, you'll need to name these columns in order to create a pivot table.)Click Insert, and select Pivot table.
In the Create pivot table panel that appears, choose if you want to insert your pivot table into a new sheet or an existing sheet. Then click Create.
Google Sheets will populate a pivot table wherever you tell it to (a new or existing spreadsheet). And this is where you'll be working from.
How to edit a pivot table in Google Sheets
When Google Sheets first populates your pivot table, a Pivot table editor panel will automatically appear. This is where you'll customize your pivot table to build different reports.
If you close the editor but need to access it again, hover over the pivot table, and then click Edit.
The editor offers two ways to analyze your data: use Google Sheets' suggestions or manually choose your table dimensions.
Use Google Sheets' suggested data analyses
There's a good chance Google Sheets knows what you want to know before you even know you want to know it—it is a Google product, after all. In the Pivot table editor panel, you'll find a list of Suggested ways to analyze your data set. (If you don't see the list, click the down caret [∨] beside Suggested.)
For example, Google Sheets suggests the following analyses for our example data set example:
Average of Hours Spent for each Project Type
Count of Client Name for each Project Type
Sum of Amount Billed for each Project Type
If you click on any of the suggested options, Google Sheets will automatically build your pivot table. For example, if I click Sum of Amount Billed for each Project Type, Google Sheets will populate the project types in column A and the total amount billed for each in column B.
And if you want to modify the table, you can edit the settings in the Pivot table editor panel.
Customize your data analysis
If the suggested analyses aren't what you're looking for—or if you'd like to perform a different type of analysis—you can manually build your pivot table by customizing four options in the editor: Rows, Columns, Values, and Filters.
How to customize your pivot table in Google Sheets
There are no fewer than 19,329 ways to customize a pivot table in Google Sheets. To help you understand how to use each pivot table element, I'll show you how to edit the pivot table to analyze how much we billed in 2023 for each client across different project types. Conveniently enough (it was definitely by design), each element that we're looking for—client, project type, total amount billed, and year—lines up nicely with our editor options.
Rows and columns help you build the two-dimensional data set on which you can calculate your third dimension values. In this instance, our base data is Client Name (row) and Project Type (column).
The value we want to get in the cells where Client Name and Project Type meet is Total Amount Billed.
The filter allows you to analyze only a specific subset of data. In this case, we want to use data from only 2023.
Before we can build the report, we need to start with an empty pivot table. If you used one of Google Sheets' suggested analyses in the previous step, clear your selections by clicking X beside the Rows and Values options.
Now let's get to actually building this thing. As a reminder, this is the question we're asking:
How much did we bill in 2023 for each client across different project types?
Add rows to your pivot table
First, we need to set up our table to show both the list of clients and project types. In the Pivot table editor panel, beside Rows, click Add, and select Client Name.
Google Sheets automatically takes the source data for Client Names, removes any duplicates, and populates a list of unique values as rows in your pivot table. By default, the client names will appear in alphabetical order (A to Z).
Of course, all you've done so far is add an existing column to your pivot table. You'll need to add more data if you really want to get value from your report.
Add columns to your pivot table
The next step is to add Project Type as the columns. In the editor, next to Columns, click Add, and select Project Type. Now your table should display each project type as separate column headers across columns B to G.
Add values to your pivot table
Now we need Google Sheets to populate the total amount billed for each client and project type.
In the editor, next to Values, click Add, and then click Amount Billed. Make sure that the dropdown under Summarize by is set to SUM. This way, you get the total amount billed versus, say, the average amount billed.
Now the pivot table is giving us some usable information: the total amount billed for each type of project we've completed for a given client.
And to help us understand the total amount billed to each client and the total amount billed for a given project type across all clients, Google Sheets automatically calculates the grand total in column G.
Add filters to your pivot table
We're one step away from answering our original question. Now we need to add a filter to only show values for 2023. (Note: you can filter data in your pivot table based on any column in your source data.)
In the editor, next to Filters, click Add, and select Year. By default, all available years (2022 and 2023) from the source data are selected. Deselect 2022, and click OK.
That's it! Now you have a pivot table that answers the question, "How much did we bill in 2023 for each client across different project types?"
How to read a Google Sheets pivot table
Using the pivot table we created in the section, we can now answer almost any question we have about the source data. This is the final step to becoming a pivot table pro.
To help you understand how to read a Google Sheets pivot table, we'll work through two more example questions.
Which client did we bill the most in 2023?
Which project type had the highest hourly rate on average?
Which client did we bill the most in 2023?
To answer this question, we need only the names of our clients as rows and the sum of the amount billed to them as values. The good news is that this means we have to simplify our pivot table.
If it's not already visible, open your Pivot table editor panel.
In the Columns section, next to Project type, click X to remove this option.
In the Rows section, within the Client Name option, click the down caret (
∨) under Sort by. Then click SUM of Amount Billed.
The pivot table automatically updates to display the grand total of the amount billed, per client, in ascending order. Now we know that in 2023, the company we billed the most, at a grand total of $1,700.00, was Questindustries.
Which project type had the highest hourly rate on average?
To answer this question, we're going to shift our analysis from looking at the total amount billed to the highest average hourly rate for each project type.
Before we dive in, open your Pivot table editor panel, and remove every option except for the existing filter.
Note: Source data may only be used once per option in the Pivot table editor panel. For example, if you add Project Type as your rows, you can't then use this data for your columns.
Beside Rows, click Add. Then click Project Type.
Beside Values, click Add. Then click Hourly Rate.
In the Values section, under Summarize by, click the down caret (
∨), and click AVERAGE.
Now we have our answer: the highest average hourly rate charged in 2023 was $68.00 for Copy Editing.
How to refresh a pivot table in Google Sheets
Let's say you've edited your original source data. These changes should automatically be reflected in your pivot table. But if that's not the case, click the Refresh icon in your web browser.
If you've added new rows or columns of data to your original source data, however, a simple refresh won't do the trick. Instead, update your pivot table's data range to include the new rows or columns.
Hover over the pivot table, and click Edit.
In the Data range field of the Pivot table editor, enter the new data range.
That's it.
How to create and use pivot tables in Google Sheets: FAQs
Still have questions about how to use pivot tables in Google Sheets? Check out the answers to these frequently asked questions so you can get the most out of your spreadsheets.
Do pivot tables update automatically in Google Sheets?
If you edit cell values in your original source data and those cells are captured within your pivot table's selected data range, these updates will automatically be reflected in your pivot table.
If, however, you add new rows or columns of data outside of your pivot table's selected data range, you'll need to manually update your pivot table's data range to capture these new values.
Can a pivot table pull from multiple worksheets?
Pivot tables in Google Sheets can only reference a single worksheet. If you want your pivot table to reference data from multiple worksheets, you need to first combine that data into one worksheet. Then, you can create a pivot table as you normally would.
Can you have two pivot tables in one sheet?
You can insert multiple pivot tables into one Google Sheets worksheet.
Create a pivot table as you normally would.
In the Create pivot table panel that appears, select Existing sheet, and enter the worksheet and cell where you want to add your new pivot table.
Click Create.
Can you merge two pivot tables?
There's no way to merge two pivot tables in Google Sheets, but there is a workaround. Combine the original source data for both pivot tables into one worksheet, and then create a new pivot table.
Automate Google Sheets
With pivot tables, it's easy to make sense of large data sets—no complicated formulas needed. Once you've gotten a firm grasp of the basics, use Zapier to connect Google Sheets to your other go-to apps, so you can automate even more of your spreadsheet-related tasks. For example, you can automatically add lead data and information from form submissions to existing spreadsheets.
Here are a few Zap templates to get you started.
To get started with a Zap template—what we call our pre-made workflows—just click on the button. It only takes a few minutes to set up. You can read more about setting up Zaps here.
Collect new Typeform responses as rows on Google Sheets
Create Google Sheets rows for new Google Ads leads
Generate rows in Google Sheets for new Unbounce submissions
Related reading:
This article was originally published in September 2018 by John Thomas. The most recent update was in March 2024
