How to Create Pivot Table in Excel for Different Worksheets

We store our data in different sheets for convenience and clarity. But at the same time, we need to create PivotTable including all the sheets. If you are curious to know how you can create PivotTable in Excel from different worksheets, then this article may come in handy for you. In this article, we discuss how you can create Pivot Table in Excel from different worksheets with an elaborate explanation.

Table of Contents hide

Download Practice Workbook

An Example to Create a Pivot Table in Excel

3 Easy Ways to Create Pivot Table in Excel for Different Worksheets

1. Using PivotChart Wizard

2. Setting Relationships Between Two Tables

3. Applying Power Query

How to Create Pivot Table from Multiple Sheets with Same Columns in Excel

Conclusion

Download Practice Workbook

Download this practice workout below.

Create Pivot Table for Different Worksheet.xlsx

An Example to Create a Pivot Table in Excel

It is easy to use recommended PivotTables option for creating Pivot Tables. But you might prefer to create a Pivot Table. Prior to Excel 2013, manually creating a Pivot Table was your only option. Here, I am going to describe to you how to create a Pivot Table in Excel with a detailed example. If you are unfamiliar with the elements of a Pivot Table, read Pivot Table terminology.

The following steps will guide you on how to create a Pivot Table in Excel with an Example.

Step 1: Insert Pivot Table in Excel

In this first step, I will show you how you can insert a Pivot Table in Excel.

Firstly, select the data range.
Secondly, go to the Insert tab from Ribbon.
Thirdly, select PivotTable.
Finally, from the dropdown menu, click From table / Range.

Subsequently, a dialog box named PivotTable from the table on the range will appear.

After that, select New Worksheet if it is not selected already.
Next, select OK.

Now, a Pivot Table will be inserted into a new worksheet.

So, the Pivot Table is now inserted in our worksheet

Step 2: Add Fields to Pivot Table

In this step, I will show you how to add fields to the Pivot Table. Here, I will work on the PivotTable Fields task pane to lay out the Pivot Table.

After inserting Pivot Table the PivotTable Fields will appear on the right side of the screen. The pivotTable Fields task pane has two parts: the upper part, where the field names reside, and the lower part, where you will place the field names from the upper part.

You can lay out the Pivot Table in the following ways:

Firstly, select and drag the field names (at the top of the PivotTable Fields task pane) to one of the four boxes at the bottom of the task pane. Here, I dragged the Amount field into the Values area. The Pivot Table will display the total of all the values in the Sum of Amount column.
Secondly, select and drag the field names you want in your Row Labels into the Rows area. Here, I selected the Branch, Opened By, and Account Type fields in the Rows area.

The following figure gives me the desired Pivot Table. From this Pivot Table, you can easily find out the grand total of the amount opened in the Westside Branch or any other Branch.
Here, I have hidden the gridlines to give the Pivot Table a better view.

Tip: Typically, the PivotTable Fields task pane is docked on the right side of your Excel window. You can drag its title bar to move it anywhere in your Excel window. If you click a cell outside the Pivot Table, the task pane will temporarily hide.

Read More: Example with Excel Pivot Table

3 Easy Ways to Create Pivot Table in Excel for Different Worksheets

Here we are going to use two separate types of datasets of which we are going to create the Pivot Table. The first dataset contains sales information regarding the regions. and the second dataset contains the sales records of how much revenue and profit were made over different transactions.

1. Using PivotChart Wizard

The PivotTable and pivot chart wizard has the capability to form a single Pivot Table from two different worksheets.

Steps

Her, in the dataset, you can see that there is data about sales. These data are going to be divided into different sheets. From those different sheets, we will create a Pivot Table using the Pivot Table wizard.

We have the sales information of Middle East Asia placed in the very next worksheet.

We have the sales information of North America placed in the very next worksheet.

We have the sales pieces of information of Europe placed in the very next worksheet.

We have the sales pieces of information of the South Asia placed in the very next worksheet.

Then press Alt+D on the keyboard, and a small board like the image below will appear,
After then, press P on the keyboard.

After pressing the buttons on the keyboard, a window like the one below will show up.
Select Another PivotTable report or PivotChart report in the Where is the data you want to analyze options.
Right after this, click on the PivotTable in the What Kind of report do you want to create options.
Click Next after this.

Then another new window will open named PivotTable and Pivot Chart Wizard.
Select I will create the page fields options.
Click Next after this.

Another new window will open, here you need to add the range of cells to the box, that you intend to create Pivot Table. From the first sheet (Sales Information of Middle East Asia), we select the range of cells C4:J7.
And then click on the Add options.
Then select 1 in the How many page fields do u want.
The name of the selected range using the below.

Before naming the selected range, make sure to select the added range.
Click Next after this.

In a similar way, select and add the other 4 sheets of data range from which you wish to create a Pivot Table.
Name every single one of them. Click Next after this.

There will be a final window after this.
Select New worksheet, and click Finish.

There will be a new PivotTable containing all the data of sheets.
And the fields also are there.
But we can see that we have the count of the values of the sheet.
We need to have the Sum instead of it.

We then switch the Field value from Count to Sum.
The Pivot Table will look like the below image.

There is a custom filter button on top of the Table.
We select South Asia from that filter.
The table will look like the table below.

And this is how we create a common Pivot Table from different worksheets in Excel using the pivot table wizard.

2. Setting Relationships Between Two Tables

The Pivot Table has the capability to create relationships that can combine two tables from different worksheets to join together and form a singular Pivot Table.

Steps

We have the following dataset in our worksheet.
We need to convert this data in to table first.
To do this, we selected the range of cells B4:D11, and then from the Insert tab, click on Table from the Tables group.

In the next box, we need to select the range of tables.
And click OK after this.
Don’t forget to check the My table has headers box.
Click OK after this.

Then rename the table from the Table Design tab to Quantity_Cost.

Do the same for the second table and rename it to Revenue_Profit.

Then go to the Insert tab and click on the PivotTable.
From the dropdown menu, click on the From Table and Range.

In the next dialog box, click on the New Worksheet in the Choose where you want the PivotTable to be placed.
And select the Quantity_Cost table in the Table/Range.
Lastly, click on Add this data to the Data Model.
Click OK after this.

Then we got the Pivot Table with all the fields from both of the tables presented here.

Let’s try to drag the Product Id from the Quantity_Cost table in the Row 4 field.
And then drag the Revenue column to the Column field.
A small yellow highlight window will appear on the fields.
Click on Create after this.

A new window open named Create Relationship.
Then select the Quantity_Cost as the first table and Revenue_Profit as the second table.
In both cases, select Product Id as the column.
Click OK after this.

Then you will notice that the table now have a new sheet and the sheet has the PivotTable fields room from both of the sheet table like before.
Now drag the Revenue and Profit Columns to the Value field.
And the same time drags the Quantity and Cost.
The Pivot Table now shows the values perfectly.

And this is how we create a common Pivot Table from different worksheets in Excel setting relationships among them.

Similar Readings

3. Applying Power Query

In the first step, we would like to convert the datasets into tables. Using table format helps you define the name. Then, you can use the word to get the table in the power query. Follow the steps carefully.

Steps

In the first dataset, select the range of cells B4 to D11.
Then, go to the Insert tab on the ribbon.
Select Table from the Tables group.

As a result, the Create Table dialog box.
As you selected the range of cells previously, it appears there automatically.
Check on My table has headers.
Finally, click on OK.

As a consequence, you will get the following result. See the screenshot.
In a similar manner, create the second table from the Cost and Quantity column.

Select any cell on the first table.
Then, go to the Data tab on the ribbon.
Select From Table/Range option from the Get & Transform Data group.

As a result, it will take the Table3 table into the power query.
To create the connection, select the Home tab on the ribbon.
Then, select the Close & Load drop-down option from the Close option.
After that, select the Close & Load To option from the Close & Load drop-down option.

The Import Data dialog box will appear.
Then, select Only Create Connection from Select how you want to view this data in your workbook section.
Finally, click on OK.

As a result, it will create a connection with the name of the table and appear in the Queries.
Select any cell on the second table.
Then, go to the Data tab on the ribbon.
Select From Table/Range option from the Get & Transform Data group.

As a result, it will take the Table4 table into the power query.
To create the connection, select the Home tab on the ribbon.
Then, select the Close & Load drop-down option from the Close option.
After that, select the Close & Load To option from the Close & Load drop-down option.

You can notice that there are connections created between two tables.

First, select the Data tab on the ribbon.
Then, select Get Data drop-down option from the Get & Transform Data group.
After that, from Combine Queries option, select Merge.

As a result, the Merge dialog box will appear.
From the drop-down option, select the Table3 table and then select the Table4 table from the second drop-down option.
Then, select the Product Id Column for both tables to create a connection.
Finally, click on OK.

As a result, it will take us to the power query. See the screenshot.

Next, to view our intended columns, select the two-sided arrow in the header.
And tick the Quantity and Cost box.
Click OK after this.

Now you have all the necessary columns.

Select the Home tab on the ribbon.
Then, select the Close & Load drop-down option from the Close option.
After that, select the Close & Load To option from the Close & Load drop-down option
The Import Data dialog box will appear.
Then, select PivotTable Report from Select how you want to view this data in your workbook section.
After that, select the New Worksheet option to put the data.
Finally, click on OK.

Finally, we will get the following result. See the screenshot.

And this is how we create a common Pivot Table from different worksheets in Excel using power query.

How to Create Pivot Table from Multiple Sheets with Same Columns in Excel

Sometimes you can have multiple datasets scattered in multiple worksheets. And you may also want to make a Pivot Table from those sheets. If you have one common column among them, then you can make you combined Pivot Table with all of the datasets across the sheets.

Here, we have Product Id as the common column among the two datasets.

Steps

We have the following dataset in our worksheet.
We need to convert this data in to table first.
To do this, we selected the range of cells B4:D11, and then from the Insert tab, click on Table from the Tables group.
Then click on the From Table/Range.

After adding the table, we need to rename the table according to our needs.
Then rename the table from the Table Design tab to Quantity_Cost from the Table Design tab.

Repeat the same process for the second table also.
Then rename the table to Revenue_Profit from the Table Design tab.

Then go to the Insert tab and click on the Pivot Table.
From the dropdown menu, click on the From table and Range.

In the next dialog box, click on the New Worksheet in the Choose where you want the PivotTable to be placed.
And select the Quantity_Cost table in the Table/Range.
Lastly, click on Add this data to the Data Model.
Click OK after this.

Then we got the Pivot Table with all the fields from both of the table presented here.

Let’s try to drag the Product Id from the Quantity_Cost table in the Row field.
And then drag the Revenue column to the Column field.
A small yellow highlight window will appear on the fields.
Click on Create after this.

A new window open named Create Relationship.
Then select the Quantity_Cost as the first table and Revenue_Profit as the second table.
In both cases, select Product Id as the column.
Click OK after this.

Then you will notice that the table now have a new sheet and the sheet has the PivotTable fields room from both of the sheet table like before.
Now drag the Revenue and Profit Columns to the Value field.
And the same time drags the Quantity and Cost.
The Pivot Table now shows the values perfectly.

And this is how we create a common Pivot Table from different worksheets in Excel with having one column common.

Conclusion

To sum it up, the issue of how we can create PivotTable in Excel from different worksheets is answered here by 3 different examples.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.

How to Create Pivot Table in Excel for Different Worksheets

Download Practice Workbook

An Example to Create a Pivot Table in Excel

3 Easy Ways to Create Pivot Table in Excel for Different Worksheets

1. Using PivotChart Wizard

2. Setting Relationships Between Two Tables

3. Applying Power Query

How to Create Pivot Table from Multiple Sheets with Same Columns in Excel

Conclusion

Related Articles

Khan Muhammad Kawser

How to Hide Filter Arrows from Pivot Table in Excel (3 Easy Ways)

How to Clear Pivot Cache in Excel (3 Effective Ways)

How to Join Pivot Tables in Excel (with Easy Steps)

What Is the Use of Pivot Table in Excel (13 Useful Examples)

Leave a reply Cancel reply