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:
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:
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.
The following tutorials explain how to perform other common operations in Excel: