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

We have two Pivot Tables: Income and Cost.

Our merged Pivot Table will look like the image shown below:

How to merge two pivot tables in Excel

Step 1 – Create Two Different Pivot Tables

  • Select the range of cells B4:D14.
  • In the Insert tab, click on the drop-down arrow of the PivotTable option from the Table group and select the From Table/Range option.

Insert Pivot Table to Merge

  • A small dialog box called Pivot Table from table or range will appear.
  • Choose the New Worksheet option.
  • Click OK.

  • A new worksheet will open with the Pivot Table.
  • Drag the Name field in the Rows area and the Income field in the Values area.
  • The Pivot Table with data will appear.

First Pivot Table of Our Dataset

  • In the Pivot Table Analyze tab, rename the Pivot Table according in the Properties group. We set our Pivot Table name as Income.
  • Format the Income Pivot Table however you want.

Formatting Pivot Table

  • Create another Pivot Table for the cost dataset. Instead of the New Worksheet option, set the destination of the Pivot Table in the Existing Worksheet and define the Location to keep both Pivot Tables in one sheet. For the second Pivot Table, we chose cell E3.

  • You will get both tables on the same sheet.

Create Two Different Pivot Tables to Merge Two Pivot Tables

Step 2 – Convert Both Pivot Tables into Conventional Tables

  • Create a new sheet using the Plus (+) sign located in the Sheet Name Bar.

  • Rename the sheet. We set our sheet name as Tables.
  • 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.
  • Right-click and paste the dataset as Values.

Pasting the Pivot Table to Marge Them

  • You will see the dataset on that sheet.

  • Select the range of cells B2:C12 and press Ctrl + T to convert the data range into a table.
  • A small dialog box titled Create Table will appear.
  • Check the option My table has headers.
  • Click OK.

Converting the data range into table to merge both tables

  • You can rename the table in the Table Design tab, from the Properties group. We set our table name as Income.
  • Format the table if you want.

Renaming and formatting the Excel normal table

  • Convert the second data range into a table as well.

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

Step 3 – Establish a Relationship Between Both Tables

  • Go to the Data tab.
  • Select the Relationships option from the Data Tools group.

Establish Relationships Between Both Tables to Merge Two Pivot Tables

  • A dialog box called Manage Relationships will appear.
  • 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.
  • In the Related Table field, choose the Cost table, and in the Related Column (Primary) field, select the Name option.
  • Click OK.

Choosing suitable options to built proper relationships

  • Click the Close button to close the Manage Relationship dialog box.

Step 4 – Merge Two Pivot Tables

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

Applying Existing Connections Command to Join two Tables

  • The Existing Connections dialog box will appear.
  • 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.
  • Choose the Pivot Table Report option and set the destination in New Worksheet.
  • 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.

  • 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

Download the Practice Workbook

<< Go Back to Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

  1. Hello. A colleague and I have both tried separately to work through these steps but get an error at the last step. The second column – Total Cost – shows the sum from the second table against each row i.e. $140033.01 for each of the people. Is there a setting in Excel we have to adjust or is there some other problem? This feature would be very useful for us if it were to work as described. Thanks for reading, hope to hear back from you.

      Thank you for sharing your problem with us. We have got a simple solution for your problem. It seems to us that you might be selecting the Name field from the Income table. That’s why Sum of Total Cost column is getting filled with 140033.01.
      Merge Two Pivot Tables Comment Solution-1
      To solve this problem, make sure to mark the Name field from the Cost table and leave the Name field from the Income table unmarked. This will solve the problem and you will be able to merge different tables as you wanted.
      Merge Two Pivot Tables Comment Solution-2
      We hope, our solution will work for your problem. If you face further difficulties with merging two pivot tables in Excel, you are always welcome to inform us.
      Sourav Kundu

Leave a reply

Advanced Excel Exercises with Solutions PDF