Create Pivot Table from Multiple Sheets

Today you will teach yourself how to create a Pivot Table from multiple sheets. You may need this lesson when you want to analyze data which you store in many sheets.

First prepare data table. Next create a Table from each set of data. Go to Insert -> Table.

Create Table

Define a name for each table. Just type the name in and press Enter. See on the picture below to find the name's place.

Table Name

Do it for every table you got.

Next create a Pivot Table. Go to Insert -> Pivot Table. Dialog box pops out. Make sure to tick Add this data to data model box.

Create PivotTable Data Model

Pivot Table is ready. Thanks to the option ticked this is a special one. Pay attention that after clicking All you can find your created tables here.

Multiple Sheets Pivot Table

That's not all! You need to create Relationships here. 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.

Create Relationship

Your Pivot Table is ready. Feel free to create one for data from Multiple Sheets.