How to Create Pivot Table from Multiple Sheets

20Oct

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.

Table of Contents

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.

pivot table Create Relationship