Table of Contents
Pivot tables are a powerful feature in Excel that allow you to quickly summarize large amounts of data from multiple sheets. To create a pivot table from multiple sheets, first select the sheets from which you want to pull data, then click the “Pivot Table” button on the Insert tab. From there, you can select the columns you want to include in your table, choose the type of data to be summarized, and customize the table to your liking.
The following step-by-step example shows how to create a pivot table from multiple sheets in Excel
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 FILTER formula to do so:
=FILTER(week2!A2:C11, week2!A2:A11<>"")
We can type this formula into cell A12 of the week1 sheet:
This formula tells Excel to return all of the rows from the week2 sheet where the value in the range A2:A11 of that sheet is not blank.
All of the rows from the week1 and week2 sheets are now consolidated into one sheet.
Step 3: Create the Pivot Table
In the new window that appears, type in the following information and then click OK:
In the PivotTable Fields panel that appears on the right side of the screen, drag Store to the Rows box, drag Product to the Columns box, and drag Sales to the Values box:
The following pivot table will automatically be created:
The final pivot table includes data from both the week1 and week2 sheets.