How to Combine Two Pivot Tables in Excel

Trying to merge two separate Pivot Tables in Excel often feels like you're hitting a wall. You have valuable data in different places - maybe sales performance in one table and marketing campaign costs in another - and you just want to see them together in one report. This guide will show you how to do it efficiently, using Excel’s built-in tools to create a single, unified Pivot Table without wrestling with endless VLOOKUPs.

Why Would You Need to Combine Pivot Tables?

Before jumping into the "how," let's quickly cover the "why." Most business data isn't stored in one giant, perfectly organized spreadsheet. It's usually scattered across different tables or even different systems. The need to combine this data arises from common questions like:

How did our Facebook Ad spend for each campaign affect product sales during the same week?
What is the relationship between salesperson activity recorded in our CRM export and their actual sales figures from our accounting software export?
How do our inventory levels for certain product categories line up with the sales volume for those same categories?

Answering these questions requires looking at two different datasets simultaneously. To make this tangible, let's use a simple example throughout this tutorial. Imagine you run an online store. You have two separate tables:

Sales Data: A table with columns for Date, ProductName, UnitsSold, and Revenue.
Marketing Data: A table listing daily marketing campaigns with columns for Date, CampaignName, and Cost.

Our goal is to create a single Pivot Table that shows Total Revenue and Marketing Cost side-by-side, grouped by date or campaign. This will help us see if a spike in ad spend led to a spike in revenue.

The Old Way vs. The Better Way

For years, the go-to method for combining data in Excel was to use formulas like VLOOKUP or INDEX/MATCH. You’d create a massive, new "master table" by pulling data from one table into the other. Sound familiar? While this sometimes works for small datasets, it's incredibly inefficient and prone to problems:

It's slow: Adding thousands of VLOOKUPs can grind your workbook to a halt.
It's fragile: One broken formula can cause a cascade of #N/A errors.
It's static: If you add new data to one of your source tables, you have to drag formulas down and pray nothing breaks.
It bloats your file size: Duplicating data into a master table makes your Excel file huge.

The modern, and much better, approach is to use Excel's Data Model and Power Query. These tools let you connect tables virtually without ever merging them into one sheet. Think of it as teaching Excel how your different tables relate to each other so they can work together in harmony.

Step-by-Step: Combining Tables with the Data Model

Here’s the complete process for combining our two imaginary tables, Sales Data and Marketing Data. This method works on modern versions of Excel (Excel for Microsoft 365, Excel 2016, and newer).

Step 1: Format Your Data as Official Excel Tables

Before you do anything else, you need to tell Excel that your data ranges are official tables. This is crucial as it makes the data dynamic - when you add new rows, they are automatically included in any analysis.

Click anywhere inside your first data range (e.g., your sales data).
Press Ctrl + T on your keyboard (or go to the Insert tab and click Table).
Make sure the "My table has headers" box is checked, and click OK.
With the new table selected, a Table Design tab will appear in the ribbon. In the top-left, give your table a descriptive name like "SalesData" instead of the default "Table1". Clear names make everything easier later.
Repeat this entire process for your second data range, naming it something like "MarketingData".

Step 2: Load Each Table into the Data Model

Next, we need to add these two tables to Excel’s internal Data Model. This is where Power Query comes in. It acts as the gateway to the Data Model but we won’t be doing any complex transformations right now - we're just using it to load our tables.

Click on any cell within your first table (SalesData).
Go to the Data tab on the ribbon.
In the "Get & Transform Data" group, click From Table/Range.
The Power Query Editor window will pop up. For now, we'll keep it simple. Just glance at your columns to make sure the data types look right (e.g., numbers are whole numbers, dates are dates). Power Query is usually smart about this.
Now for the most important part: Instead of clicking the big "Close & Load" button, click the small dropdown arrow on it and select Close & Load To....
In the "Import Data" dialogue box, select Only Create Connection. Then, at the bottom, check the box that says Add this data to the Data Model. Click OK.

You’ll now see a "Queries & Connections" pane on the right side of your worksheet showing the connection you just made. Repeat these exact steps for your second table (MarketingData).

Once you’re done, both tables will be loaded into the Data Model, existing as connected sources of data without cluttering up your workbook with new sheets.

Step 3: Create the Relationship Between the Tables

This is where the magic happens. We need to tell Excel how these two tables are related. To do this, they must have at least one column in common. In our example, both the SalesData and MarketingData tables have a Date column. This common column is our "key."

Your "key" column in at least one of the tables must contain unique values. In our example, the MarketingData table should ideally have only one row per date (one campaign per day), making it the "one" side of the relationship. The SalesData table can have many sales per day, making it the "many" side. This is called a one-to-many relationship and is the most common type.

Go to the Data tab and click on the Relationships icon (it looks like several interconnected tables).
In the "Manage Relationships" dialog box, click New....
The "Create Relationship" window will open.
For the first Table, select your "many" side table (in our case, SalesData) from the dropdown. For Column (Foreign), select the common column, Date.
For the Related Table, select your "one" side table (MarketingData). For Related Column (Primary), select the common column again, Date.
Click OK, then Close.

You have now officially linked your tables! Excel understands that a date in the marketing table corresponds to all the sales that happened on that same date.

Step 4: Build Your Combined Pivot Table

Now for the payoff. With the relationship defined, you can build a Pivot Table that pulls fields from both tables as if they were one.

Go to the Insert tab and click PivotTable.
In the "Create PivotTable" dialog box, you'll see a new option. Select From Data Model.
Choose where you want your new Pivot Table to go (New Worksheet is usually best) and click OK.

Look at your PivotTable Fields list on the right. You should now see both tables, SalesData and MarketingData, listed. You can click the small arrow next to each to expand their fields.

Now, you can build your report:

From the MarketingData table, drag CampaignName to the Rows area.
From the MarketingData table again, drag Cost to the Values area.
From the SalesData table, drag Revenue to the Values area right alongside Cost.

And there you have it! A single Pivot Table displaying data from two separate sources, neatly organized. You can now analyze how much revenue was generated for each marketing campaign launched.

Quick Tips for Success and Troubleshooting

Keep Your "One" Side Unique: A relationship won't work correctly if the key column on your "one" side has duplicate values. If your marketing table had multiple campaigns on the same day, you would need a more granular key (like 'Campaign ID') or create a separate, unique lookup table (like a 'Calendar Table').
Data Cleanliness Matters: Ensure the values in your common columns match exactly. "Campaign A" is not the same as "campaign a". The same goes for extra spaces or formatting differences in dates. Use Power Query to standardize these fields before loading them if you run into issues.
Diagram View is Your Friend: For more complex scenarios with multiple tables, go to the Power Pivot tab > Manage. This opens up the Data Model window. In the Home tab of this window, click Diagram View. It gives you a visual way to drag and drop to create relationships, which can be much more intuitive.

Final Thoughts

Moving away from messy VLOOKUPs and embracing Excel's Data Model is a game-changer for anyone who regularly works with different datasets. By formatting your data into official tables, loading them into the model, and establishing relationships, you can build sophisticated, dynamic, and refreshable reports without creating oversized, error-prone spreadsheets.

While this process is a huge leap forward, pulling data from multiple external sources like Google Analytics, Shopify, and your CRM can add a whole new layer of complexity. At Graphed, we handle all that data connection and modeling for you. Instead of setting up tables and relationships manually, you just connect your marketing and sales platforms, and then you can ask in plain English, "Show me my Facebook Ads cost versus Shopify revenue by campaign for last month." We instantly build a live, interactive dashboard that answers your question, turning hours of data prep into a 30-second task.