Sometimes when working with spreadsheets, you may have data spread across multiple sheets.
You may want to create a single pivot table from this data. This can be done in Google Sheets with a formula.
In this tutorial, I will show you how to create a pivot table from multiple sheets in Google Sheets.
Table of Contents
Consolidate Data From Multiple Sheets into One Sheet
We cannot create a pivot table from multiple sheets. Instead, what we need to do is consolidate all of our sheets into one sheet. We can do that by copying and pasting, but in most cases, a formula is the best method.
We can use the QUERY function to consolidate data from multiple sheets into one sheet in Google Sheets.
I will show you how this is done by combining together two sheets that I want to create a pivot table with.
Here is the first sheet:
Here is the second sheet:
To use the QUERY function to consolidate these sheets, the syntax will be:
=QUERY({sheet1_range;sheet2_range;sheet3_range})
When you use this function, you will be placing it in cell A1 of a new sheet.
Here is how this looks in a spreadsheet:
You can see that this formula has simply combined that data from my two sheets into one.
The exact formula I used in this is:
=QUERY({EastWest!A1:G9;NorthSouth!A2:G9})
My sheet names were EastWest and NorthSouth, which is why those are used in the formula.
Also, notice how the second range starts at A2 whereas the first range starts at A1. This is because I already have the header rows in with the first argument, so I have excluded them from the second.
Creating the Pivot Table
Once you have got all of your sheets consolidated into one sheet, you can now create your pivot table on that sheet.
Highlight your data and in the top menu select Data>Pivot table
Make sure “new sheet” is selected and click on the Create button.
Your pivot table will then be created and you can begin selecting the data that you want to view.
Here is my pivot table with the item and sales selected:
Closing Thoughts
Creating a pivot table from multiple sheets is pretty straightforward—you simply need to get all of your sheets into one somehow.
You can either manually copy and paste them all together, or you can use a function like QUERY to consolidate the multiple sheets into one.
After all your data exists on one sheet you then create the pivot table on that sheet.
More Google Sheets Tutorials:
How to Remove Grand Total from a Pivot Table
How to Sort a Pivot Table
How to Make a Table Chart