How to Merge Two Pivot Tables in Excel (with Quick Steps)

Pivot Table is an amazing feature of Excel, where we can show our large dataset in a summarize from according to our requirement. Sometimes, we need to merge two Pivot Tables. In this article, we will show you the step-by-step procedure to merge 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 workbook for practice while you are reading this article.


Step-by-Step Procedure to Merge Two 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.

After completing all the steps, our merge Pivot Table will look like the image shown below:

How to merge two pivot tables in Excel


Step 1: Create Two Different Pivot Tables

In our first step, we will create two different Pivot Tables, which we will merge later. The procedure is explained below step-by-step:

  • First of all, select the range of cells B4:D14.
  • Now, in the Insert tab, click on the drop-down arrow of the Pivot Table option from the Table group and select the From Table/Range option.

Insert Pivot Table to Merge

  • As a result, a small dialog box called Pivot Table from table or range will appear.
  • In this dialog box, choose the New Worksheet option.
  • Finally, click OK.

  • A new worksheet will open with the Pivot Table.
  • Then, drag the Name field in the Rows area and the Income field in the Values area.
  • The Pivot Table with data will appear in front of you.

First Pivot Table of Our Dataset

  • In the Pivot Table Analyze tab, rename the Pivot Table according to your desire from the Properties group. We set our Pivot Table name as Income.
  • After that, format the Income Pivot Table according to your desire.

Formatting Pivot Table

  • Similarly, create another Pivot Table for the cost dataset. However, instead of the New Worksheet option, this time, set the destination of the Pivot Table in the Existing Worksheet and define the Location to keep both Pivot Tables in one sheet. For our second Pivot Table, we choose cell E3.

  • At last, you will get both tables on the same sheet.

Create Two Different Pivot Tables to Merge Two Pivot Tables

Thus, we can say that we have completed the first step to merge two Pivot Tables in Excel.

Read More: How to Merge Two Tables in Excel (5 Methods)


Step 2: Convert Both Pivot Tables into Conventional Tables

In the following step, we will convert both Pivot Tables into our conventional Excel table. The process is shown below:

  • At first, create a new sheet using the ‘Plus (+)’ sign located in the Sheet Name Bar.

  • Now, rename the sheet according to your desire. We set our sheet name as Tables.
  • Then, in the Pivot Table sheet, select the range of cells B3:F13 and press ‘Ctrl+C’ to copy the Pivot Tables.

Copy Both Pivot Table to Combine Them

  • Go back to the Tables sheet.
  • After that, right-click on your mouse and paste the dataset as Value.

Pasting the Pivot Table to Marge Them

  • 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.

Converting the data range into table to merge both tables

  • 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.

Renaming and formatting the Excel normal table

  • Similarly, convert the second data range into a table.

Convert Both Pivot Tables into Conventional Tables to Merge Two Pivot Tables

So, we can say that we have finished the second step to merge two Pivot Tables in Excel.

Read More: How to Merge Tables from Different Sheets in Excel (5 Easy Ways)


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.

Establish Relationships Between Both Tables to Merge Two Pivot Tables

  • As a result, a dialog box called Manage Relationships will appear.
  • Then, click on the New option.

Create new relationship between two Excel table to merge them

  • 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.

Choosing suitable options to built proper relationships

  • 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 merge two Pivot Tables in Excel.

Read More: How to Merge Two Tables in Excel with Common Column (5 Ways)


Step 4: Merge Two Pivot Tables

In the final step, we will generate our merged Pivot Table. The steps to complete the task are given below:

  • Firstly, in the Data tab, select the Existing Connections option from the Get & Transform Data.

Applying Existing Connections Command to Join two Tables

  • 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.

Extracting data from the Excel tables

  • 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 merged Pivot Table.

Merge Two Pivot Tables

Finally, we can say that we have completed the final step, and we are able to merge two Pivot Tables in Excel.

Read More: How to Merge Two Tables Based on One Column in Excel (3 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 merge 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!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo