Pivot Table From Multiple Sheets

Updated on December 31, 2023
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel Pivot Table From Multiple Sheets

PivotTables from multiple sheets are a concept where we should add two or more tables to one table. Then, we can select the fields according to the requirement from one place.

In a word, it means different tables. Data can be appended from other sheets using some techniques and shortcuts.

How to Create a Pivot Table from Multiple Sheets?

Implementing PivotTables from multiple worksheets has some different processes. This process can also be done in different ways. The following are the other methods to create a PivotTable from multiple sheets in Excel.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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

Process #1

The no. of sheets should be two or more.

For this process, we need data on multiple worksheets. E.g., Sheet1, Sheet2, Sheet3, etc.

Below are the steps to create a PivotTable from multiple sheets:

  1. First, click “Alt+D,” then click “P.” The following dialog box will appear.

    pivot table with multiple sheets example 1.1

  2. Select “Multiple consolidation ranges” in that dialog box and click “Next.”

    multisheet pivot table example 1.2

  3. After clicking “Next,” it will go to Step2. In that, select “I will create the page fields” as in the below dialog 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, it will open another step after clicking the “Next” button as shown below.

    multisheet pivot table example 1.4

  5. In that, select the range of table 1, click the “Add” button, and select the range of another table in another sheet. Then, click “Add.” Now, it will add the two tables to a single table.

  6. Select the page field as “1” below the “All ranges” option. Then give the names to the two sheets as per the better understanding so that if we click table 1, it will show the table name in the PivotTable for better knowledge.

    multisheet pivot table example 1.5

  7. There is an option to select whether we will insert the PivotTable in a new or existing worksheet. A new worksheet will be selected, as shown in the figure.

    multisheet pivot table example 1.6

  8. Finally, click on “Finish.” Now, it will create the PivotTable in a new worksheet.

    multisheet pivot table example 1.7

  9. By default, it will show the “Count of Value.” We should change the value in the headings for the sum. For that, go to any cell which contains a value and right-click. Then, select the “Summarize Values” option.

  10. After this, we can remove the columns we need and do not need. We can do this by selecting the “Column Labels.” Then, we can check the column labels, whichever is necessary.

  11. Now, there is the other column for grand totals. In general, we do not need the grand totals of columns. Hence, that can be removed by right-clicking on the value and selecting “PivotTable Options.” A dialog box will appear, as shown below.

    multisheet pivot table example 1.8

  12. Go to the “Totals & Filters” tab and uncheck the option “Show grand totals for rows.” If necessary, we can retain it or uncheck the option for it. Now, click on “OK.”

    multisheet pivot table example 1.9

  13. Now, the grand totals will only be present for the columns.

    multisheet pivot table example 1.10

  14. The final step would be changing the name of the PivotTable. Again, it can be user-defined or related to the data in the PivotTable.

    It is the first process to create a PivotTable from multiple sheets in Excel. In this PivotTable, as in the normal PivotTable, only the fields from the right side can be dragged 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 tables. It will act as the primary key for the first table and the foreign key for the second table.

Here, we will use Sheet 4 and Sheet 5 to create a PivotTable 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, it will insert a table in the data. The name will be displayed on the table in the left corner. This process can be done for all the tables containing the data.
multisheet pivot table example 2.1

It will add filters in default. If we do not need them, we can turn them off by clicking the “CTRL+SHIFT+L” shortcut from a keyboardShortcut From A KeyboardAn Excel shortcut is a technique of performing a manual task in a quicker way.read more or going to the “Data“ tab and clicking on the “Filters” option. Then the filters will be disabled.

These are the steps to be followed to create a PivotTable for the following data.

  • Step 1: Click on the “Insert” tab and “PivotTable.” A dialog box will appear now, and you will be asked whether we should create the PivotTable in a new 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 excelUse A New Sheet Option In ExcelWhen we open a new workbook, there are three worksheets available by default. However, if you want to insert a new worksheet into the workbook, the shortcut key is Shift + F11.read more.

  • Step 2: Lastly, check the box “Add this data to the Data Model.” It is an important step in this PivotTable creation process.
multisheet pivot table example 2.4
  • Step 3: A PivotTable will be created now in the new worksheet. On the right side, we may have all the fields related to the PivotTable.
multisheet pivot table example 2.5
  • Step 4: Go to the “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 to be added to the PivotTable. The corresponding column is the same in both tables. It is from the first table, also called the primary key. A column is the same in the second column, 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 per the requirement. We can choose the fields from all the tables in the PivotTables. For that, it will first ask to create a new PivotTable.
multisheet pivot table example 2.10
  • Step 8: Click “Yes.” We can select fields from all the tables to create a PivotTable.
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 pressing the”CTRL+T” shortcut keys and removing filters. Now, Go to the “Data” tab -> click on “From Other Sources “ -> From “Microsoft Query “-> Excel Files.
Consolidate table example 3.2
Consolidate table example 3.3
  • Step 2: Select the worksheet in which you have created the data.
Consolidate table example 3.4
  • Step 3: This step will show another window to select the tables of all the sheets in the workbook. The tables can be chosen as per the requirement, as shown below. We have chosen sheets 5 and 6.
Consolidate table example 4
  • Step 4: After selecting the tables from the sheets, click “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.

Consolidate table example 3.6
  • Step 6: Go to the “File” menu and 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 PivotTable, select the option “ PivotTable Report “ and click on “OK.”
Consolidate table example 3.12
  • Step 9: The PivotTable is ready. We can create it as per the requirement.
Consolidate table example 3.13

Note: The reason for inserting a table into the data is that if, in the future, any data is appended in the last, then the Pivot Table can be refreshedPivot Table Can Be RefreshedTo refresh pivot tables, you may use the following methods - refresh pivot table by changing data source, refresh pivot table using right click option, auto-refresh pivot table using VBA Code, refresh pivot table when you open the workbook.read more by new data automatically by just refreshing the page.

Things to Remember

Click “Customize Quick Access Tool Bar”Customize 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, go to “More Commands” -> Popular Commands -> Select PivotTables -> click on “ADD.”

It will add the PivotTable option to the quick access toolbar for easy access.

This article is a guide to Excel PivotTable from Multiple Sheets. Here, we discuss creating a PivotTable from multiple sheets in Excel with examples and downloadable Excel templates. You may also look at these useful functions in Excel: –