Table of Contents
Creating a pivot table in Excel allows for effective analysis and organization of data from multiple sheets. To create a pivot table from multiple sheets, first, select all the sheets that contain the data you want to include. Then, go to the “Insert” tab and click on “Pivot Table.” In the pop-up window, choose the option to use multiple consolidation ranges, and click “Next.” Select the data range for each sheet and click “Add.” Finally, choose where you want the pivot table to be placed and click “Finish.” This will generate a new sheet with a pivot table that combines the data from all the selected sheets.
Excel: Create Pivot Table from Multiple Sheets
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I create a pivot table from multiple sheets in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-from-multiple-sheets-in-excel/
stats writer. "How can I create a pivot table from multiple sheets in Excel?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-from-multiple-sheets-in-excel/.
stats writer. "How can I create a pivot table from multiple sheets in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-from-multiple-sheets-in-excel/.
stats writer (2024) 'How can I create a pivot table from multiple sheets in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-create-a-pivot-table-from-multiple-sheets-in-excel/.
[1] stats writer, "How can I create a pivot table from multiple sheets in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I create a pivot table from multiple sheets in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.