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
To create the pivot table, click the Insert tab, then click PivotTable within the Tables group.
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel Pivot Table: Calculate Sum & Count of Same Field
Excel: How to Create Pivot Table Based on Filtered Data
Excel: How to Apply Multiple Filters to Pivot Table at Once
