We store our data on 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 to create a Pivot Table in Excel for different worksheets, then this article may come in handy for you. In this article, we discuss how you can create a Pivot Table in Excel for different worksheets with an elaborate explanation.
Here, I have attached an image of a created pivot table for different sheets. For conducting the session, I’m going to use Microsoft 365 version.
An Example of Creating a Pivot Table in Excel
It is easy to use the 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.
Step 1: Insert Pivot Table in Excel
- Select the data range >> go to the Insert tab >> select PivotTable >> from the dropdown menu >> click From Table/Range.
So, a dialog box named PivotTable from table or range will appear.
- Select New Worksheet if it is not selected already >> press OK.
Now, a Pivot Table will be inserted into a new worksheet.
Step 2: Add Fields to Pivot Table
Here, I will work on the PivotTable Fields task pane to lay out the Pivot Table.
After inserting the 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:
- 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 Customer ID into the Values area. The Pivot Table will display the total of all the values in the Count of Customer ID column.
- Similarly, select and drag the field names you want in your Row Labels into the Rows area. Here, I selected the Gender, and Occupation type fields in the Rows area.
The first portion of the above figure gives me the desired Pivot Table. From this Pivot Table, you can easily find out the grand total of the listed customers.
How to Create Pivot Table in Excel for Different Worksheets: 3 Easy Ways
Here we will use some datasets of which we will create the Pivot Table. Basically, there are two different types of datasets. The first dataset contains customer information regarding their age, gender, and occupation. The second dataset contains the purchase details. There are some more datasets about the purchase list of individual customers.
1. Using PivotTable and PivotChart Wizard to Create Pivot Table for Different Worksheets
The PivotTable and PivotChart Wizard have the capability to form a single Pivot Table from different worksheets.
Here, we have some data about individual customer purchase lists in different sheets. From those different sheets, we will create a Pivot Table using the PivotTable and PivotChart Wizard.
We have the purchase list of customer ID H1612001 placed in the worksheet named H1612001. Similarly, we have two other worksheets.
- Now press Alt+D on the keyboard >> After that, press P on the keyboard.
After pressing the buttons on the keyboard, a dialog box named PivotTable and PivotChart Wizard – Step 1 of 3 will show up.
- Select Multiple consolidation ranges 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 dialog box will open named PivotTable and PivotChart Wizard – Step 2a of 3.
- Select I will create the page fields options.
- Click Next after this.
Another new dialog box will open, here you need to add the range of cells to the box that you intend to create Pivot Table.
- From the sheet named H1612001, we select the range of cells C4:G9.
- And then click on the Add options.
- Then select 1 in the How many page fields do you want?
- Give a name for the selected range in the Field one: box. Here, I named it H1612001.
- In a similar way, select and add the other 2 sheets of data range from which you wish to create a Pivot Table.
- Name every single one of them.
- Before naming the selected range, make sure to select the added range.
- Click Next after this.
There will be a final window after this.
- Select New worksheet >> click on Finish.
Finally, there will be a new Pivot Table containing all the data of sheets. And the fields also are there. But we can see that we have the Count of Value in the Values area. We need to have the Sum instead of it.
So, we switch the Field value from Count to Sum. And the Pivot Table will look like the image below.
This is how we create a common Pivot Table from different worksheets in Excel using the pivot table wizard.
There is a Custom Filter button on top of the table. We select H1612002 from that filter. So, the table will look like the table below.
2. Setting Relationships Between Two Excel Tables for Creating Pivot Table Using Different Worksheets
Sometimes you can have multiple datasets scattered in multiple worksheets. And you may also want to make a Pivot Table from those sheets.
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.
If you have one common column among them, then you can make a combined Pivot Table with all of the datasets across the sheets.
Here, we have Customer ID as the common column among the two datasets. To do so, we need to convert the dataset into a table first.
- Now, select any cell of the dataset >> and then from the Insert tab >> click on Table from the Tables group.
So, a dialog box named Create Table will appear.
- You will get the range selected and My table has headers box checked.
- Click OK after this.
As a result, you will get the table.
- Then, rename the table. To do so, you need to go to the Table Design tab.
- To get the Table Design tab, click on any cell from the table.
- Now, go to the Table Design tab >> write a name in the Table Name: box.
- Similarly, make another Excel table with the data range of purchase history. And give it a name.
- Select any cell of the Customer_Details table >> Then go to the Insert tab >> click on the PivotTable >> From the dropdown menu >> click on the From Table/Range.
- In the dialog box named PivotTable from table or range, you will get the Table/Range: box auto-filled.
- Click on the New Worksheet in the Choose where you want the PivotTable to be placed.
- Then, you must check to Add this data to the Data Model.
- Press OK after this.
Then we got the Pivot Table in a new worksheet with all the fields from both of the tables presented here.
- Let’s try to drag the Customer ID and Gender from the Customer_Details table in the Rows field.
- Then, drag the Product Category column from the Purchase_Details table to the Rows field and the Purchase Amount column to the Values field.
- A small yellow highlight window will appear on the fields. Click on Create in that.
A new dialog box opens named Create Relationship.
- Then select the Purchase_Details as the first Table and Customer_Details as the Related Table.
- In both cases, select Customer ID as the column.
- Click OK after this.
Then, you will notice that the Pivot Table shows the values perfectly. This is how we create a common Pivot Table from different worksheets in Excel setting relationships among them.
3. Applying Power Query to Make Pivot Table for Different Worksheets
First, 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.
- So, convert the data range into Excel tables and give them specific names. You can see the steps of method-2 for making and naming the table.
- Then, select any cell of the Customer_Details table >> go to the Data tab >> from the Get & Transform Data group >> click on the From Table/Range.
As a result, it will take the Customer_Details table into the Power Query.
- To create the connection, select the Home tab on the ribbon.
- Then, select the Close & Load drop-down option >> choose the Close & Load To 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 & Connections.
- Select any cell on the second table (Purchase_Details).
- Then, go to the Data tab >> from the Get & Transform Data group >> select the From Table/Range option.
So, it will take the Purchase_Details table into the Power Query.
- To create the connection, select the Home tab >> from the Close & Load drop-down option >> select the Close & Load To option.
Again, we got the Import Data dialog box.
- So, select Only Create Connection from Select how you want to view this data in your workbook section >> Press OK.
Now, you can notice that there are connections created between the two tables.
- Then, go to the Data tab >> from the Get Data drop-down option >> go to Combine Queries option >> select Merge.
As a result, the Merge dialog box will appear.
- From the 1st drop-down option >> select the Customer_Details table >> from the 2nd drop-down option >> choose the Purchase_Details table.
- Then, select the Customer ID column for both tables to create a connection.
- Finally, press OK in that dialog box.
So, it will take us to the power query again.
- Next, to view our intended columns, select the two-sided arrow in the header named Purchase_Details >> uncheck Customer ID and Use original column name as prefix.
- After this, click OK.
- Then, go to the Home tab >> from the Close & Load drop-down option >> select the Close & Load To option.
The Import Data dialog box will appear.
- 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 >> click on OK.
Finally, we will get a Pivot Table in a new worksheet. You must drag the fields into the areas to get the result.
So, this is how we create a common Pivot Table from different worksheets in Excel using Power Query.
How to Create Multiple Pivot Tables on One Sheet in Excel
In this section, I will create multiple pivot tables on one sheet. See the below steps.
- Click any cell of the data table >> from Insert tab >> select Recommended PivotTables.
So, the dialog box named Recommended PivotTables will pop up.
- Choose your preferred Pivot Table >> press OK.
As a result, you will see the following Pivot Table. Click on the plus (+) sign to see the details of Row Labels.
- Go to the Insert tab >> from PivotTable >> select From Table/Range.
So, the PivotTable from table or range appears.
- Write another table name in the Table/Range box >> choose Existing Worksheet >> set the Location >> press OK.
Here, you must set the location while keeping enough space for the 1st Pivot Table.
Lastly, we will get another Pivot Table in that worksheet. You must drag the fields into the areas to get the output of the 2nd Pivot Table.
Frequently Asked Question
1. What is the benefit of using a Pivot Table in Excel?
Answer: With the help of a Pivot Table you can find a summation, average value, count of data, and so on mathematical operations at a glance. Basically, you can analyze the data properly and easily with the Pivot Table. Also, you can filter the data and get value according to that. Whenever you have a large dataset, you should use the Pivot Table. Then, you can handle your data within a very short period.
2. Can I update the data source for a Pivot Table in Excel?
Answer: Yes, you can. After updating the data source, firstly click on any cell in the Pivot Table >> go to PivotTable Analyze >> press Refresh.
Things to Remember
- Try to convert the dataset into Excel tables before making a Pivot Table.
- All the time, you have to manually drag the fields into areas in the PivotTable Fields task to get the valued Pivot Table.
- If you don’t see the PivotTable Fields task panel, then click on Pivot Table >> go to PivotTable Analyze >> press Field List.
- In the Rows area, maintain a sequence. Because the 1st inserted column in the Rows area will get the first priority. After that, all other inserted columns will be subgrouped under that 1st column.
Download Practice Workbook
Download this practice workout below.
To sum it up, the issue of how to create a Pivot Table in Excel for 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.
- Creating a Pivot Table Automatically in Excel
- How to Create Pivot Table Report in Excel
- How Do I Create a Pivot Table from Multiple Worksheets