How to Consolidate Several Sheets into a Single Pivot Table
Learn how to use a pivot table for data from multiple sheets in Google Sheets. We'll teach you how to merge sheet data with the QUERY function, allowing you to have all your data in one pivot table.
The Pivot Table is Google Sheets’ answer to Microsoft Excel’s PivotTable feature. This feature allows the user to quickly summarize a large amount of structured data through few clicks, giving the user a powerful tool for free. Pivot Table can be a bit more challenging to tame, but this tutorial will help you master it by considering the common demands of data analysis.
The issue of selecting ranges across multiple sheets
There are times where the data is divided into several sheets in the same file. How can we consolidate them into a single Pivot Table in Google Sheets? For our example, we need to combine the data from two sheets week27 and week28 to a single Pivot Table.
You might try combining the ranges by clicking on the symbol of four boxes beside the range of cells at the top of the Pivot table editor.
Unfortunately, the Pivot table editor does not allow combining ranges from different sheets of the same file.
So, how can we solve this problem?
Well, we can combine them all into a single sheet and then add a Pivot Table to read from the new sheet.
If you have guessed it, the good old copy-paste method still works!
But, let us use a quicker and cleaner method involving the QUERY function. For this technique, you need to know the range of values of your sheets. For our example, we note the range of the following sheets:
- Week27: A1 to H633 (the first row contains the column labels)
- Week28: A2 to H450 (we did not include the first row as it is the same as that of week 27)
We can now do the following steps:
Step 1: Create a new sheet. Label it accordingly.
Step 2: On the cell A1 (the first cell), type the following:
=QUERY({range_in_sheet_one,range_in_sheet_two,range_in_sheet_three})
For our example, it will look like this:
=QUERY({week27!A1:H633;week28!A2:H450})
This will automatically load all the data within the range.
Step 3: Click on any cell, and then select Data on the main menu, then Pivot Table on the drop-down menu. Then once the Pivot Table is loaded, you can customize it to display relevant information.
