How to Create Pivot Table from Multiple Sheets

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.

pivot table Create Table

Give each table a meaningful name in the “Table Name” box, like “SalesDataJan” or “SalesDataFeb”.

pivot table Table Name

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).

Create PivotTable Data Model

After creating the PivotTable, you’ll see the “PivotTable Fields” pane on the right.

Multiple Sheets Pivot Table

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.

pivot table 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.

See also  How to add text to formula?

pivot table Create Relationship

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.