In this tutorial, you will learn how to create a pivot table from multiple Google Sheets
How to Create Pivot Table from Multiple Google Sheets
A Pivot Table is a powerful tool that allows you to summarize large amounts of data in a an easy-to-read tabular format. The data is sorted and organized in a way that makes it easy to extract meaningful insights from it.
We can use pivot tables to summarize consolidated data from multiple sheets. For example, you may have data from different sources that you want to combine and analyze together.
In this guide, we will show you how to create a Pivot Table from data coming from two or more sheets.
How to Consolidate Data from Multiple Sheets using a Google Sheets Pivot Table
Here’s how to consolidate data from two or more sheets using a Google Sheets pivot table.
Step 1
First, we’ll need to consolidate all the data we want to include in our pivot table. We should also ensure that each cell range follows the same header order.
It also follows that each cell range should have the same number of rows.
In this example, we have transaction data that comes from two different sources. Each table is stored in a different sheet in our Google Sheet document.
Click on the ‘+’ icon in the bottom panel of your Google Sheets window.
Step 2
In the new spreadsheet, select any empty cell. This cell will hold our QUERY function.
Step 3
The QUERY function can be used to consolidate multiple cell ranges into a single table. We’ll start by typing “=QUERY(“ to start the function.
We’ll type an open brace to indicate that we’ll be defining a new array. The array will compose of the two or more cell ranges we want to consolidate.
Step 4
Next, type the first cell range you would like to add. Since we’ll be referencing a cell range outside the current sheet, we’ll need to indicate the sheet name in the cell reference.
You may also navigate to the source sheet tab and use your cursor to select the range manually.
Step 5
Perform the previous step for each cell range you want to add to the final pivot table. Separate each cell reference with a semi-colon.
Why do we use a semicolon? When creating arrays, we separate rows by using the ‘;’ symbol. This will ensure that our ranges are combined vertically.
Step 6
After adding all the different cell ranges to our array, we can close the array with a closing brace. Hit the Enter key to evaluate the function yourself.
Step 7
Now that we have consolidated our data, we can now start creating our pivot table. Select the entire table by typing Ctrl + A.
Step 8
In the Insert menu, select the Pivot table option.
Step 9
In the Create pivot table dialog box, you may select whether to add the pivot table in a new sheet or an existing sheet. After selecting your choice, click on Create to proceed.
Step 10
You should now have a Pivot table with data coming from multiple sheets.
Since the QUERY function is dynamic, any changes made to the source sheets will reflect in the final pivot table.
Summary
This guide should be everything you need to create a pivot table from multiple Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.