Excel Pivot Table From Multiple Sheets
Pivot Tables from multiple sheets is 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.
For this process, we need data on multiple worksheets. Eg: Sheet1, sheet2, sheet3 etc.
The no. of sheets should be two or more than 2.
- Step 1: Click Alt+D, then click P. the following dialogue box will appear.
- Step 2: In that dialogue box, select Multiple consolidation ranges, and click NEXT.
- Step 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.
- Step 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,
- Step 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.
- Step 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.
- Step 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.
- Step 8: Finally, Click on Finish. Now the Pivot table will be created in a new worksheet.
- Step 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 “Summarize values by” option and in
- Step 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.
- Step 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.
- Step 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.
- Step 13: Now the Grand Totals for Columns only will be present for the Columns.
- Step 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.
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 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 sheet 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.
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 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 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 same sheet.
It is good to use a new sheet option.
- Step 2: Lastly check the box “Add this table to the Data Model”. This is an important step in this Pivot Table creation process.
- Step 3: Pivot Table will be created now in the new worksheet and in that right side we have all the fields related to the Pivot Table.
- Step 4: Goto “Analyze” tab -> Relationships -> New.
- 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 foreign key.
- Step 6: Now click ok.
- 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.
- Step 8: Click Yes and you can select fields from all the tables to create a pivot table.
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 the 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.
- Step 2: Then select the worksheet in which you have created the data.
- 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.
- Step 4: After selecting the tables from the sheets, click on Next, you will get this dialog box and click on OK.
This dialog box says that without joining the tables 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“.
- Step 6: Now Go to File Menu, select “ Return Data to Microsoft Excel “
- Step 7: Now you will get a window of “ Import Data “.
- Step 8: In this window, as we want to create a Pivot table select option “ Pivot Table report “ and click on OK.
- Step 9: Now the Pivot table is ready and we can create it as per the requirement.
Note: The reason for inserting a table into the data is, if in the case in 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 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 a 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 Bar, 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.
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 –