Rubayed Razib Suprov
Pivot Table is an amazing feature of Excel, where we can show our large dataset in a summary according to our requirements. Sometimes, we need to merge two Pivot Tables. In this article, we will show you the step-by-step procedure to Join two Pivot Tables in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Step-by-Step Procedure to Join Pivot Tables in Excel
In this article, we will show you the step-by-step procedure to merge two Pivot Tables. We have two Pivot Tables: Income and Cost. For avoiding any compatibility issues, use MS Excel 365 edition.
STEP 1: Create Two Different Pivot Tables
Before we delve into joining two separate tables, we need to create those two pivot tables from the general table first.
- We have two separate tables below, of which we need to create a pivot table.
- And later we will join them to a new single table.
- Then select the range of cells B4:D14 and then go to Insert > PivotTable > From Table /Range.
- Then in the new window, you will notice that the range of cell B5:D14 has already been selected in the Table/Range range box.
- Then choose a New Worksheet in the choice where you want PivotTable to place.
- Click OK after this.
- After clicking OK, you will notice that there is a new worksheet with the fields in the right side.
- From there, drag the Name field to the Rows section and the Income field to the Values section.
- After this you will see the pivot table is now formed.
- After this change the column name of the pivot table by double-clicking over the header
- Now we have to repeat the same process again.
- For this return to the original sheet and select the range of cells F4:F15 and go to Insert Pivot Table > Form Table / Range.
- Then in the new window, you will notice that the range of cell B5:D14 has already been selected in the Table/Range range box.
- Then choose an Existing Worksheet in the Choose where you want PivotTable to be placed. In the Location, enter or select E2 in the newly created Tables worksheet.
- Click OK after this.
- Then following the previous pivot table, drag the Name field in the rows and the Cost in the Values sections.
- Now you can see both of the tables now present.
Read More: How to Join Tables in Excel (6 Suitable Methods)
STEP 2: Convert Both Pivot Tables into Conventional Tables
In the following step, we will convert both newly created Pivot Tables into our conventional Excel table. The process is shown below:
- To begin with, in the Pivot Table sheet, select the range of cells B3:F13 and press ‘Ctrl+C’ to copy the Pivot Tables.
- Go to the Tables sheet.
- After that, right-click on your mouse and paste the dataset as Values.
- You will see the dataset on that sheet.
- Afterward, select the range of cells B2:C12 and press Ctrl+T to convert the data range into a table.
- As a result, a small dialog box titled Create Table will appear.
- Check the option My table has headers.
- Finally, click OK.
- If you want, you can rename the table in the Table Design tab, from the Properties group. We set our table name as Income.
- Moreover, format the table according to your desire.
- Similarly, convert the second data range into a table.
- So, we can say that we have finished the second step to join two Pivot Tables in Excel.
Read More: How to Use Join Formula in Excel (6 Practical Examples)
STEP 3: Establish Relationship Between Both Tables
Now, we are going to establish a relationship between our tables. The relationship establishment procedure is given as follows:
- First, go to the Data tab.
- Now, select the Relationships option from the Data Tools group.
- As a result, a dialog box called Manage Relationships will appear.
- Then, click on the New option.
- Another dialog box titled Create Relationship will appear.
- In the Table field, select the Income table from the drop-down option, and in the Column (Foreign) field, set the Name option.
- Similarly, in the Related Table field, choose the Cost table, and in the Related Column (Primary) field, select the Name option.
- Finally, click OK.
- Click the Close button to close the Manage Relationship dialog box.
- Our job is completed.
- Hence, we can say that we have accomplished the third step to join two Pivot Tables in Excel.
Read More: How to Perform Outer Join in Excel (2 Easy Ways)
STEP 4: Join Two Pivot Tables
We created the two separate pivot tables and also created the relationship among them also. Now we can join them together,
- Firstly, in the Data tab, select the Existing Connections option from the Get & Transform Data.
- As a result, the Existing Connections dialog box will appear.
- Now, from the Tables tab, select the Tables in Workbook Data Model option and click on Open.
- Another dialog box titled Import Data will appear.
- Then, choose the Pivot Table Report option and set the destination in New Worksheet.
- Finally, click OK.
- The Pivot Table will show in a new sheet, and both tables will show in the field list.
- Click on each table name to see the fields that belong to them.
- Now, drag the Name field in the Rows area and the Income and Cost field in the Value area.
- You will get the final joined Pivot Table.
- Finally, we can say that we have completed the final step, and we are able to join two Pivot Tables in Excel.
Read More: How to Perform Left Join in Excel (3 Easy Ways)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to join two Pivot Tables in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or suggestions.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
