Table of Contents
Creating a pivot table from multiple sheets in Google Sheets is a simple process. With the data from each sheet stored in its own tab, you can select the entire data set and create a pivot chart with a single command. The chart will then automatically update as you add or modify data in the individual sheets, allowing you to quickly analyze data from multiple sources.
The following step-by-step example shows how to create a pivot table from multiple sheets in Google Sheets.
Step 1: Enter the Data
Suppose we have a spreadsheet with two sheets titled week1 and week2:
Week1:
Week2:
Suppose we would like to create a pivot table using data from both sheets.
Step 2: Consolidate Data into One Sheet
Before we can create a pivot table using both sheets, we must consolidate all of the data into one sheet.
We can use the following QUERY formula to do so:
=QUERY({week1!A1:C11;week2!A2:C11})
Here’s how to use this formula in practice:
Notice that the data from the week1 and week2 sheets are now consolidated into one sheet.
Step 3: Create the Pivot Table
To create the pivot table, we’ll highlight the values in the range A1:C21, then click the Insert tab and then click Pivot table.
The final pivot table includes data from both the week1 and week2 sheets.
The following tutorials explain how to perform other common operations in Google Sheets: