How to 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.

Creating pivot tables across multiple sheets

First prepare data table. Next create a Table from each set of data. Go to Insert -> 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.

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.

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.

Create a relationships between sheets

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.

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.