Add a Second Table to Pivot Tables: A Comprehensive Guide

Lauren Aug 08, 2026

Expanding analytical capabilities in Microsoft Excel often involves using pivot tables, which provide a summary of data and allow for data manipulation. However, you may find that your pivot table needs additional data that's not currently included. In such cases, you'll want to learn how to add a second table to your pivot table. This process, known as consolidating data, combines data from two or more tables into a single, manageable pivot table.

Learn How to Work Smarter With Pivot Tables Today
Learn How to Work Smarter With Pivot Tables Today

Before proceeding, ensure both tables contain matching data. For instance, they could both have a 'Salesperson' column, allowing you to align data based on this common factor.

Add a Running Total in a Pivot Table
Add a Running Total in a Pivot Table

Consolidating Data from Two Tables

To add a second table to your pivot table, you'll first need to consolidate the data from both tables. This process involves creating a new table that incorporates data from both original tables.

Excel Pivot Table Explained (Part 1) | Easy Guide for Beginners with Example
Excel Pivot Table Explained (Part 1) | Easy Guide for Beginners with Example

Here's a step-by-step guide to consolidating data:

Step 1: Create a New Worksheet

Management training, Soft skills, Power BI, Excel Learning solutions
Management training, Soft skills, Power BI, Excel Learning solutions

Begin by creating a new worksheet where you'll consolidate the data from your two tables. To do this, click on the desired cell, then navigate to the 'Home' tab and click on "+" under 'Insert', then 'Worksheet'. Name this new sheet, e.g., "ConsolidatedData".

Step 2: Insert Structured Reference

Now, you'll use the 'GETPIVOTDATA' function to insert structured reference data from the pivot table into the new worksheet. This function retrieves data from a pivot table based on given conditions.

How to Update an Excel Pivot Table - Even if the Source Data Changes (+ video tutorial)
How to Update an Excel Pivot Table - Even if the Source Data Changes (+ video tutorial)

The function syntax is: GETPIVOTDATA(area_name, table_name, field1, condition1, field2, condition2, field3, condition3, ...)

Here's an example of how to use 'GETPIVOTDATA' if your pivot table is named 'PivotTable1' in Sheet1, and you're pulling data from the 'Salesperson' and 'Sales' columns:

=GETPIVOTDATA("PivotTable1", "Sheet1!Sheet1!$A$2:$F$10", "Salesperson", "Bob", "Sales")

How to Add Slicers to Pivot Tables in Excel in 60 Seconds | Envato Tuts+
How to Add Slicers to Pivot Tables in Excel in 60 Seconds | Envato Tuts+

Inserting the Consolidated Data into the Pivot Table

Now that you have consolidated your data, you can insert it into your pivot table. This process involves adding new fields to your pivot table.

How to Sort a Pivot Table in Excel (Sorting Pivot Tables)
How to Sort a Pivot Table in Excel (Sorting Pivot Tables)
How to create a new pivot table style
How to create a new pivot table style
Dynamic Pivot Table with Multiple Tables using Power Query
Dynamic Pivot Table with Multiple Tables using Power Query
101 Time-Saving Pivot Table Tricks For Smarter Reports
101 Time-Saving Pivot Table Tricks For Smarter Reports
Advanced Pivot Table Techniques (to achieve more in Excel)
Advanced Pivot Table Techniques (to achieve more in Excel)
Using Pivot Tables to Analyze Income and Expenses
Using Pivot Tables to Analyze Income and Expenses
Count Unique Values in a Pivot Table
Count Unique Values in a Pivot Table
How to create a pivot table in Excel to slice and dice your data
How to create a pivot table in Excel to slice and dice your data
How To Make Use of 5 Advanced Excel Pivot Table Techniques | Envato Tuts+
How To Make Use of 5 Advanced Excel Pivot Table Techniques | Envato Tuts+
Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one
Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one
You Won't Believe These Crazy PIVOT TABLE Hacks!
You Won't Believe These Crazy PIVOT TABLE Hacks!
Excel Formulas Unleashed
Excel Formulas Unleashed
a blue and white poster with the words which pivot table to use on it
a blue and white poster with the words which pivot table to use on it
50 Things You Can Do With Excel Pivot Table | Free Microsoft Excel Tutorials
50 Things You Can Do With Excel Pivot Table | Free Microsoft Excel Tutorials
How to apply a pivot table style
How to apply a pivot table style
How to Delete or Remove a Pivot Table in Excel
How to Delete or Remove a Pivot Table in Excel
What is the use of Pivot Table and Pivot Chart in Excel
What is the use of Pivot Table and Pivot Chart in Excel
The Simple Guide to Using Pivot Tables to Understand Marketing Data
The Simple Guide to Using Pivot Tables to Understand Marketing Data
2 Quick Ways to use Excel Pivot Table group by 15 minutes
2 Quick Ways to use Excel Pivot Table group by 15 minutes

The steps to add fields to a pivot table are:

Step 1: Go to the 'Analyze' Tab

Click on any cell within your pivot table, then navigate to the 'Analyze' tab. This tab contains all the options for manipulating your pivot table.

Step 2: Add Fields

In the 'Fields' section, you'll see a list of all available fields that can be added to your pivot table. Click on the field you want to add (in this case, your new 'Salesperson' field), then click on the 'Add' button.

Your new field will now appear in the pivot table, allowing you to manipulate data in the same way as any other field.

Remember, keeping your data organized and properly structured makes consolidating data and adding tables to your pivot table much easier. Regularly reviewing and updating your data can prevent errors and make your pivot tables more reliable.

Once you've mastered adding a second table to a pivot table, you'll unlock a new level of analytical capability in Excel, allowing you to glean deeper insights from your data. Happy analyzing!