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.
Table of contents
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.
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:
- First, click “Alt+D,” then click “P.” The following dialog box will appear.
- Select “Multiple consolidation ranges” in that dialog box and click “Next.”
- After clicking “Next,” it will go to Step2. In that, select “I will create the page fields” as in the below dialog box.
- 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.
- 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.
- 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.
- 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.
- Finally, click on “Finish.” Now, it will create the PivotTable in a new worksheet.
- 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.
- 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.
- 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.
- 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.”
- Now, the grand totals will only be present for the columns.
- 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.
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.
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. 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.
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..
- Step 2: Lastly, check the box “Add this data to the Data Model.” It is an important step in this PivotTable creation process.
- 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.
- Step 4: Go to the “Analyze” tab -> Relationships -> New.
- 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.
- Step 6: Now, click on the ok.
- 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.
- Step 8: Click “Yes.” We can select fields from all the tables to create a PivotTable.
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.
- Step 2: Select the worksheet in which you have created the data.
- 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.
- Step 4: After selecting the tables from the sheets, click “Next.” You will get this dialog box and click on “OK.”
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. In that, all the data belonging to all the tables will be present. There will be two portions in that window: “ Name RangeName RangeName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window., “and ” Data Area.”
- Step 6: Go to the “File” menu and 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 PivotTable, select the option “ PivotTable Report “ and click on “OK.”
- Step 9: The PivotTable is ready. We can create it as per the requirement.
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. by new data automatically by just refreshing the page.
Things to Remember
- There should be two or more tables in one workbook for creating a PivotTable.
- If any new data is added to any of the tables after creating a PivotTable, then to reflect the changes, we need to refresh the PivotTable manually.
- Every time we create a PivotTable, we should make it in a new worksheet to reflect the changes correctly.
- When we drag and drop in excelDrag And Drop In ExcelExcel Drag and Drop, also known as “Fill Handle”, is the PLUS (+) icon that appears when we move mouse or cursor to the right bottom of the selected cell. Using this plus icon we can drag to the left, to the right, to the top and also to the bottom from the active cell. , the attributes from any of the tables into four fields, it will generate the report’s summary in just a few seconds.
- The main thing in creating a PivotTable is that no cell, column, or row should be left blank if there is no value. It should be “0” or any other value. Else, the PivotTable will not be shown up correctly.
- Quick Tool Tip: The PivotTable quick reference can be added to the quick toolbar using the following process.
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., 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: –
- Examples of Pivot TableExamples Of Pivot TablePivot Table represents various statistical figures such as mean, median or mode. For example, data of any real estate project with different fields like type of flats, block names, area of the individual flats could be easily presented using pivot table.
- Slicer in Pivot TableSlicer In Pivot TablePivot Table Slicer is a tool in MS Excel to filter the data present in a pivot table. The data can be presented based on various categories as it offers a way to apply the pivot table filters that dynamically change the view of the pivot table data.
- Filter in Pivot TableFilter In Pivot TableBy right-clicking on the pivot table, we can access the pivot table filter option. Another approach is to use the filter options available in the pivot table fields.
- Delete the Pivot TableDelete The Pivot TableTo delete a pivot table in Excel, you must first select it. Then go to the Analyze menu tab under the Design and Analyze menu tabs and select actions. Then, from the Select option's drop-down option, select Entire Pivot Table to delete it.