Creating a Pivot Table from multiple sheets in Excel can be a powerful way to analyze data spread across different sources. You will learn how to make a Pivot Table from many sheets from this step-by-step guideline.
Creating pivot tables across multiple sheets
Go to each sheet containing data. Create a Table from each set of data. Go to Insert -> Table.
Consider enabling the Data Model option. The Data Model allows you to work with larger datasets, create relationships between tables, and use DAX (Data Analysis Expressions) functions for more complex calculations.
Give each table a meaningful name in the “Table Name” box, like “SalesDataJan” or “SalesDataFeb”.
Create a Pivot Table. Go to Insert -> Pivot Table. Ensure the “Use this workbook’s Data Model” option is selected (this allows you to create relationships between tables from different sheets).
After creating the PivotTable, you’ll see the “PivotTable Fields” pane on the right.
You can create calculated fields directly within your Pivot Table. These calculated fields allow you to perform on-the-fly calculations based on existing fields in your Pivot Table.
Create a relationships between sheets
You need to create Relationships. This is a column which connects your tables. If you know data bases this is such a primary key in database.
Go to Ribbon. In the PivotTable Tools tab which appeared click Analyze -> Relationships.
Now let’s create a New Relationship. For me it is id column which I have in my both tables. The most common are: order number, product number, id, name etc.
Your Pivot Table is ready. Feel free to create one for data from Multiple Sheets.
Using Consolidate feature
To create a pivot table from multiple sheets in Microsoft Excel, you can also use the “Consolidate” feature to combine the data from multiple worksheets into a single data range. Here’s how:
- Prepare your data: Ensure that the data you want to include in the pivot table is in a similar format on each worksheet, with the same columns and headings.
- Select the data: Go to the first worksheet that contains the data you want to include in the pivot table, and select the entire data range.
- Consolidate the data: Go to the Data tab on the ribbon, and click on the “Consolidate” button in the “Data Tools” section. In the Consolidate dialog box, select the “Use all sheet” option, and select the worksheets that contain the data you want to include in the pivot table.
- Create the pivot table: In the Consolidate dialog box, select the “Create PivotTable” option, and choose the location where you want to create the pivot table.
- Configure the pivot table: Excel will create a pivot table based on the consolidated data, and display the PivotTable Fields list. You can then drag and drop fields into the Rows, Columns, and Values areas to group and summarize the data in the pivot table.
The pivot table will now include data from all of the worksheets you selected, allowing you to analyze and compare the data across multiple sheets in a single pivot table. You can also refresh the pivot table to update the data whenever you make changes to the worksheets.