Pivot Table From Multiple Sheets

Excel Pivot Table From Multiple Sheets

Pivot Tables from multiple sheets are a concept where there should be two or more tables to be added to one table, and the fields can be selected according to the requirement from one place.

In one word, different tables, data can be appended from different sheets using some techniques and shortcuts.

How to Create a Pivot Table from Multiple Sheets?

Implementing Pivot Tables from multiple worksheets has some additional processes. This process can also be done in different ways. The following are the different methods to create a pivot table from multiple sheets in excel.

You can download this Pivot Table from Multiple Sheets Excel Template here – Pivot Table from Multiple Sheets Excel Template

Process #1

For this process, we need data on multiple worksheets. E.g., Sheet1, sheet2, sheet3, etc.

The no. of sheets should be two or more than 2.

  1. Click Alt+D, then click P. the following dialogue box will appear.


    pivot table with multiple sheets example 1.1

  2. In that dialogue box, select Multiple consolidation ranges, and click NEXT.


    multisheet pivot table example 1.2

  3. In this step, after clicking NEXT, it will go to step2, and in that, select “ I will create the page fields “ as in the below dialogue box.


    multisheet pivot table example 1.3

  4. After selecting the above options, click on NEXT again. The window also will be moved to the third step. In this step, another step will be opened after clicking the NEXT button as shown below,


    multisheet pivot table example 1.4

  5. In that, select the range of table 1 and then click the ADD button and then select the range of another table in another sheet and then click ADD. Now the two tables will be added to a single table.

  6. Now select the Page field as 1 below the Ranges option. Then give the names to the two sheets as per the better understanding, so that if we click table 1, then the table name will be shown in the Pivot table for better understanding.


    multisheet pivot table example 1.5

  7. Now, there is an option to select whether the Pivot table will be inserted in a new worksheet or existing sheet. By default, a new worksheet will be selected, as shown in the figure.


    multisheet pivot table example 1.6

  8. Finally, Click on Finish. Now the Pivot table will be created in a new worksheet.


    multisheet pivot table example 1.7

  9. By default, the value will be shown for Count. We should change the value in the headings for the sum. For that, go to any cell which contains value and right click and then select the “Summarize values by” option and in

  10. After this, we can remove the Columns which we actually need, and we do not need them. This can be done by selecting the “Column Labels.” We can check the column labels, whichever is necessary.

  11. Now, there is the other column for Grand Totals. In general, we don’t need the grand totals of columns. Hence that can be removed by “ right-click on the value and select “Pivot table Options,” and a dialog box will appear as shown below.


    multisheet pivot table example 1.8

  12. Goto “Totals and filters” Tab and uncheck the option ” Show Grand totals for Rows.” If that is necessary, you can retain it or else uncheck the option for it. Now click on, OK.

    multisheet pivot table example 1.9

  13. Now, the Grand Totals for Columns only will be present for the Columns.


    multisheet pivot table example 1.10

  14. The final step would be Changing the Name of the Pivot table; it can be user-defined or related to the data in the Pivot table.


    This is the first process to create a Pivot Table from multiple sheets in excel. In this Pivot table, as in the normal Pivot Table, only the fields from the right side can be drag and dropped as per the requirement.

    multisheet pivot table example 1.11

Process #2

In this method, there should be a common row in both the Tables. This will act as the Primary key for the first table and the Foreign key for the second table.

Here we will use Sheet 4, sheet 5, to create a pivot table from multiple sheets in excel.

We will analyze the second method with the help of the following example.

  • Step 1: In sheets 3 and 4, select the table, click CTRL + T to select the whole data, and draw a table for complete data. Now a table will be inserted in the data. The Name will be displayed for the table in the left corner. This process can be done for all the tables containing the data.
multisheet pivot table example 2.1

Filters will be added in default; if we don’t need them, we can turn them off by clicking CTRL+SHIFT+L  shortcut from a keyboard or simply go to the “Data “ tab and click on the Filters option. Then the filters will be disabled.

Now to create a Pivot Table for the following data, these are the steps to be followed.

  • Step 1: Click on the Insert tab and click on Pivot Tables. A Dialog Box will appear now, and in that, you will be asked whether the Pivot table should be created in a new sheet or the same sheet.
multisheet pivot table example 2.2
multisheet pivot table example 2.3

It is good to use a new sheet option in excel.

  • Step 2: Lastly, check the box “Add this table to the Data Model.” This is an important step in this Pivot Table creation process.
multisheet pivot table example 2.4
  • Step 3: Pivot Table will be created now in the new worksheet, and on that right side, we have all the fields related to the Pivot Table.
multisheet pivot table example 2.5
  • Step 4: Goto “Analyze tab -> Relationships -> New.
multisheet pivot table example 2.6
multisheet pivot table example 2.7
  • Step 5: The table is the current table. The related table is the table that is to be added to the Pivot Table. Related Column is the column which is the same in both the tables; this is from the first table; it is also called the primary key. A column is the same column in the second column, which is called a foreign key.
multisheet pivot table example 2.8
  • Step 6: Now, click on the ok.
multisheet pivot table example 2.9
  • Step 7: Now, we can select the required fields as per the requirement. The fields can be selected from all the tables in the Pivot Tables. For that, it will first ask to create a new pivot table.
multisheet pivot table example 2.10
  • Step 8: Click Yes, and you can select fields from all the tables to create a pivot table.
multisheet pivot table example 2.11

Process #3

The First and foremost thing in this method is to assign a name to all the data. We will illustrate this by an example. Below is an example of this process.

  • Step 1: Create a table for the entire data by CTRL+T and remove filters. Now Goto “Data” Tab -> click on “ From Other Sources “ -> From Microsoft Query -> Excel Files.
Consolidate table example 3.2
Consolidate table example 3.3
  • Step 2: Then select the worksheet in which you have created the data.
Consolidate table example 3.4
  • Step 3: In this step, another window will be shown to select the tables of all the sheets in the workbook. The tables can be selected as per the requirement, as shown below. We have selected Sheet No 5 and 6.
Consolidate table example 4
  • Step 4: After selecting the tables from the sheets, click on Next; you will get this dialog box and click on OK.
Consolidate table example 3.5

This dialog box says that without joining the tables, the query cannot be executed and now join the tables.

  • Step 5: Then, a window will appear, and in that, all the data belonging to all the tables will be present. In that window, there will be two portions. One is “ Name Range, “and the other is ” Data Area.”
Consolidate table example 3.6
  • Step 6: Now Go to File Menu, select “ Return Data to Microsoft Excel.”
Consolidate table example 3.14
  • Step 7: Now, you will get a window of “ Import Data. “
Consolidate table example 3.11
  • Step 8: In this window, as we want to create a Pivot table, select the option “ Pivot Table report “ and click on OK.
Consolidate table example 3.12
  • Step 9: Now, the Pivot table is ready, and we can create it as per the requirement.
Consolidate table example 3.13

Note: The reason for inserting a table into the data is, if in the case in the future, if any data is appended in the last, then the Pivot Table can be refreshed by new data automatically by just refreshing the page.

Things to Remember

  • There should be two or more tables in one workbook to create a pivot table.
  • If any new data is added in any of the tables after once creating a Pivot table, then to reflect the changes, we need to refresh the pivot table manually.
  • Every time we create a Pivot table, it should be created in a new worksheet to reflect the changes correctly.
  • When we drag and drop in excel, the attributes from any of the tables into any of the 4 fields, the summary of the report will be generated in just a few seconds.
  • The main thing in creating a Pivot table is no cell, column, or row should be left blank if there is no value either; it should be “0” or any other value. Else the Pivot Table will not be shown up correctly.
  • Quick Tool Tip: The Pivot table quick reference can be added to the quick toolbar by using the following process.

Click Customize Quick Access Tool BarCustomize Quick Access Tool BarQuick Access Toolbar (QAT) is a toolbar in Excel that may be customized and is located on the upper left-hand side of the window. It enables users to save important shortcuts and easily access them when needed.read more, goto More commands -> Popular Commands -> Select Pivot Tables -> click on ADD.

Now the Pivot table option will be added to the quick access toolbar for easy access.

Recommended Articles

This has been a guide to Excel Pivot Table from Multiple Sheets. Here we discuss how to create a pivot table from multiple sheets in excel with examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>