How to Create Pivot Table in Excel for Different Worksheets

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Create Pivot Table for Different Worksheets in Excel

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.

Creating Pivot Table

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.

Set location for a new Pivot Table

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.

An Example of Pivot Table

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.

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.

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.

Three basic datasets in three different 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.

Make Pivot table for Different Worksheets with PivotTable and PivotChart Wizard

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.

Working with PivotTable and PivotChart Wizard

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.

Adding data range in PivotTable and PivotChart Wizard

  • 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.

Adding more data in PivotTable and PivotChart Wizard

There will be a final window after this.

  • Select New worksheet >> click on Finish.

Finish work in PivotTable and PivotChart Wizard with setting location

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.

Getting Pivot Table in a new worksheet

So, we switch the Field value from Count to Sum. And the Pivot Table will look like the image below.

Changing Values Area in PivotTable fields Task Panel

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.

Filtering Pivot Table

Note: If you have alphabetical data and you choose Sum of Value in the Values area then you will get 0 in that column. One more thing, you can use different ranges of datasets.

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.

Inserting Table

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.

Creating Excel 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.

Giving Name to Excel Table

  • Similarly, make another Excel table with the data range of purchase history. And give it a name.

Rename Excel Table

  • 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.

How to Create Pivot Table in Excel

  • 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.

Create PivotTable from table or range in Excel with Adding Data Model

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

Get both Tables Data in Pivot Table

  • 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.

Create Relationship Between Excel Tables

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.

Working with Create Relationship Dialog Box

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.

How to Create Pivot Table for Different Worksheets in Excel


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.

Use Get & Transform Data group for Opening Power Query

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.

Open Power Query Editor for Customer_Details

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.

Created only Connection

As a result, it will create a connection with the name of the table and appear in the Queries & Connections.

Created Connection for Table named Customer_Details

  • 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.

Opening Power Query Editor for Purchase History

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.

Working in Power Query Editor for Purchase_Details

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.

Imported data to create Connection

Now, you can notice that there are connections created between the two tables.

Connect Two tables

  • Then, go to the Data tab >> from the Get Data drop-down option >> go to Combine Queries option >> select Merge.

Merging Queries

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.

Merging Queries to Create Pivot Table for Different Worksheets

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.

Applying Power Query to Make Pivot Table for Different Worksheets

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.

Imported data to Create Pivot Table in Different Worksheet

Finally, we will get a Pivot Table in a new worksheet. You must drag the fields into the areas to get the result.

Creating Pivot Table Using Power Query in Excel for Different Worksheets

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.

Use of Recommended PivotTable

So, the dialog box named Recommended PivotTables will pop up.

  • Choose your preferred Pivot Table >> press OK.

Select One from Recommended PivotTables

As a result, you will see the following Pivot Table. Click on the plus (+) sign to see the details of Row Labels.

Create Pivot Table Using Recommended PivotTables

  • Go to the Insert tab >> from PivotTable >> select From Table/Range.

Inserting Pivot Table from table or 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.

How to Create Multiple Pivot Tables on One Sheet in Excel

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.

Create Multiple Pivot Tables on Single Sheet


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.


Conclusion

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.


Related Articles


<< Go Back to How to Create Pivot Table in Excel | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

1 Comment

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo