How to Link Pivot Tables in Excel

Building a powerful dashboard in Excel often means controlling multiple reports from a single set of filters. The key to making this happen is linking your pivot tables so that one click in a slicer updates several charts and tables at once. This article will show you exactly how to connect different pivot tables to create a dynamic, interactive report without pulling your hair out.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

First, Why Should You Link Pivot Tables?

You have raw data - maybe sales figures, marketing campaign results, or website traffic. You’ve neatly summarized them into a few different pivot tables on a dashboard. One table shows sales by region, another shows sales by product category, and a third shows your top-performing sales reps.

The problem? They’re all siloed. If you want to see the 2023 sales figures for the East region, you have to apply that filter to the first pivot table, then the second, then the third. It's tedious, error-prone, and a huge time sink. Linking your pivot tables solves this completely. When they’re connected, you can add a single filter or slicer - say, for the year 2023 - and all your pivot tables update in an instant.

This transforms your static report into an interactive dashboard, allowing you or your team to slice and dice the data to find insights without manually adjusting every component over and over.

The Foundation: Understanding Excel’s Data Model

Before you can link anything, you need to understand the modern and most efficient way Excel handles this: the Data Model. Gone are the days of hacking solutions together. The Data Model is Excel’s built-in engine for working with one or more tables of related data.

Instead of creating pivot tables from individual, disconnected worksheets, you'll first load your source data into the Data Model. Any pivot table created from this shared data source can then be controlled by the same set of slicers and timelines. Here’s how to set it up.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Format Your Data as a Proper Table

The first rule of working with data in Excel is to stop using messy ranges and start using official Excel Tables. They are dynamic, well-structured, and a non-negotiable step for using the Data Model.

If your data is already a Table, great. If not, follow these steps:

• Click any single cell inside your dataset.
• Go to the Insert tab on the Excel ribbon and click Table (or use the shortcut Ctrl + T on Windows / Cmd + T on Mac).
• A small box will pop up confirming the data range. Ensure the "My table has headers" box is checked if your data has column titles (which it should!).
• Click OK. Your data will now be formatted with striped rows.

Now, give your table a descriptive name. With your new table selected, click on the "Table Design" tab that appears in the ribbon, and type a new name in the "Table Name" box on the far left. Let's call our main sales data table something simple like SalesData.

Repeat this process for any other source data tables you have. For example, you might have another table naming products called Products and a third with regional manager info called Regions.

Step 2: Add Your Tables to the Data Model

Once your data is in named Tables, you need to add them to the Excel Data Model. Don’t worry, this sounds technical, but it’s just a few clicks.

Go back to the PivotTable creation process, but with a slight twist:

1. Click anywhere inside your first data table (e.g., SalesData).
2. Go to the Insert tab and click PivotTable.
3. The "Create PivotTable" window will appear. Here’s the critical step: at the bottom, check the box that says "Add this data to the Data Model."
4. Choose where you want the PivotTable to go (a new worksheet is usually best) and click OK.

You'll notice your PivotTable Fields pane on the right side looks slightly different. Instead of just showing fields from your one table, it says "Active" and "All," allowing you to see all the tables in your Data Model. You've now created a pivot table that's powered by the Data Model.

To add your other tables (like Products and Regions) to the model, you don't need to create a new pivot table. The easiest way is to use the Power Pivot tab:

• Go to the Power Pivot tab in the ribbon and click Manage. (If you don't see it, you may need to enable the add-in via File > Options > Add-ins > COM Add-ins.)
• This opens the Data Model window. From here, you can click on Get External Data or find the "Add to Data Model" button within the "Tables" group on the Home ribbon to add your other named Excel tables.
• A somewhat faster approach: simply create pivot tables from your other tables (Products, Regions) and remember to check the "Add this data to the Data Model" box each time. You can delete these temporary pivot tables afterward, the tables themselves will remain in the model.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3 (Optional but Important): Create Relationships

If you're using multiple tables, you need to tell Excel how they're related. For example, your SalesData table has a 'Product ID' column, and your Products table also has a 'Product ID' column. Linking these together allows you to pull the 'Product Category' from the Products table into your SalesData pivot table.

• Go to the Data tab and click on the Relationships icon (it looks like a few connected tables).
• Click New.
• For Table, select your main data table (e.g., SalesData).
• For Column (Foreign), select the connecting field (e.g., ProductID).
• For Related Table, select your lookup table (e.g., Products).
• For Related Column (Primary), select the matching unique field (e.g., ProductID).
• Click OK.

Now your tables can work together inside any PivotTable you build from the Data Model.

Connecting Your Pivot Tables with Slicers

With the hard work of building your Data Model complete, linking the pivot tables is the easy and rewarding part. You’ll use Slicers (and/or Timelines) to control everything.

Step 1: Create Your Pivot Tables from the Data Model

Create a few different pivot tables to populate your dashboard. Just be sure to create them correctly.

• Navigate to the new worksheet for your dashboard.
• Go to the Insert tab and click PivotTable.
• In the creation window, select the option "From Data Model" or "From External Data Source". Then click "Choose Connection" and find ExcelDataModel.
• Click OK.

Now, build your pivot tables from the PivotTable Fields list. This list shows all tables in your model, so you can mix and match fields as needed. Create a few different summaries:

• PivotTable 1: Sum of 'Sales Amount' by 'Region'.
• PivotTable 2: Sum of 'Sales Amount' by 'Product Category'.
• PivotTable 3: Count of 'Orders' by 'Month'.

Step 2: Insert a Slicer

Now, let’s add a control filter.

1. Click on any one of your pivot tables.
2. Go to the PivotTable Analyze tab in the ribbon.
3. Click Insert Slicer.
4. A dialog box will appear, listing all available fields from your Data Model. Check the box for the field you want to filter with - let's use 'Year'.
5. Click OK.

A "Year" slicer will appear on your worksheet. It looks like a set of interactive buttons (e.g., 2022, 2023, 2024). Right now, clicking a year only filters the pivot table you had selected when you created it. The next step fixes that.

Step 3: Connect the Slicer to All Pivot Tables

This is where the magic happens. You’ll connect that single slicer to every report on your dashboard.

1. Right-click the header of your new slicer (where it says "Year").
2. Select Report Connections... from the menu.
3. A dialog box will pop up, listing every pivot table in your workbook that was created from the Data Model.
4. Check the boxes next to all the pivot tables you want this slicer to control.
5. Click OK.

That's it! Now, when you click "2023" on your slicer, all three of your pivot tables will instantly snap to show only 2023 data. You've successfully linked your pivot tables. You can add more slicers for Region, Product, or any other field and connect them in the same way to make your dashboard even more powerful.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

What About the GETPIVOTDATA Function?

For years, advanced Excel users relied on the GETPIVOTDATA function to pull specific values out of a pivot table cell and display them elsewhere. For example, you could use a formula in a cell to grab the grand total sales from your main pivot table.

=GETPIVOTDATA("Sales Amount", $A$3)

This tells Excel to return the "Sales Amount" from the pivot table located at cell $A$3. While it can be useful for creating highly customized report layouts or summary cards, it is not an effective method for "linking" pivot tables in a dashboard context. Changes require rewriting formulas rather than simply clicking a slicer button.

The bottom line: Use Slicers and the Data Model for building anything interactive. Keep GETPIVOTDATA in your back pocket for those rare times you need to reference a single, specific pivot table value inside a separate cell or formula.

Final Thoughts

Learning to link pivot tables using Excel’s Data Model and slicers is the bridge between making static reports and building true interactive dashboards. By setting up a proper data foundation first, you can connect multiple tables and charts to a single set of controls, making your reports easier to use and insights much faster to find.

This whole process - exporting data, cleaning it, loading it into tables, creating a Data Model, and arranging slicers - is powerful, but it’s a lot of manual setup. That's why we built Graphed. We wanted to skip straight to the insights. You connect your data sources like Google Analytics, Shopify, or Salesforce with one click, and then you just ask for what you want in plain English. Instead of clicking through menus to build a dashboard, you can simply type, "Create a dashboard showing sales per month over the last year, broken down by product category," and we instantly generate a live, interactive dashboard that does the same thing, no setup required.