Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
- Power Bi (35+)
Pivot Tables from Multiple Sheets (Table of Contents)
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 process. This process can also be done in different ways. Following are the different methods to create a pivot table from multiple sheets in excel.
Method #1 – Consolidate Pivot Table from Multiple Sheets
For this process, we need data in 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.
In that dialogue box, select Multiple consolidation ranges, and click NEXT.
Step2a: 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.
After selecting the above options click on NEXT again. The window also will be moved to the third step.
Step 2.b: In this step, another step will be opened after clicking the NEXT button as shown below,
In that select the range of table 1 and then click 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.
Now select 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 3: 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.
Finally, Click on Finish. Now the Pivot table will be created in a new worksheet.
- 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 that select “sum” instead of “Count“.
- 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 are necessary.
- 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.
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.
Now the Grand Totals for Columns only will be present for the Columns.
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.
Method #2 – Consolidate Pivot Table from Multiple Sheets
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 “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.
Step1: 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.
Step2: Lastly check the box “Add this table to the Data Model”. This is an important step in this Pivot Table creation process.
Step3: 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.
- The table is the current table. The related table is the table which 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.
Now click ok.
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.
Click Yes and you can select fields from all the tables to create a pivot table.
Method #3 – Consolidate Pivot Table from Multiple Sheets
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.
Then select 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 Goto File Menu, select “ Return Data to Microsoft Excel “
Step 7: Now you will get a window of “ Import Data “.
In this window, as we want to create a Pivot table select option “ Pivot Table report “ and click on OK.
Step 8: 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 about Pivot Table from Multiple Sheets
- 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.
You can download this Pivot Table from Multiple Sheets Excel Template here – Pivot Table from Multiple Sheets Excel Template
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 –