How to Consolidate Several Sheets into a Single 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.

Spreadsheet containing several sheets. Names of sheets are week27, week28, and items. The displayed sheet, week27, contains the list of sales, costs, and the resulting profits. — *The displayed sheet, week27, contains the list of sales, costs, and the resulting profits.*

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.

*One attempt to combine ranges of two sheets in the Pivot Table data range box...*

Unfortunately, the Pivot table editor does not allow combining ranges from different sheets of the same file.

*...that failed miserably. Pivot Table is strict in reading data from a single sheet only.*

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.