Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create Relationships in Excel Pivot Table

Cody Schneider••8 min read

Tired of wrestling with VLOOKUPs just to pull data from two different tables into a single Excel report? You're not alone. The good news is there’s a much more powerful and flexible way to combine related data using Pivot Tables without writing a single formula. This article will walk you through exactly how to create relationships between tables directly within a Pivot Table, using Excel’s built-in Data Model.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

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

Watch Graphed demo video

Why Bother with Pivot Table Relationships?

For years, the standard way to combine data in Excel was to cram everything into one giant, flat table. If your sales data was in one sheet and your product details in another, you’d spend your morning writing VLOOKUP or XLOOKUP formulas to pull product categories or prices into your sales sheet. This approach has some serious drawbacks:

  • It’s Slow: Thousands of VLOOKUPs in a single spreadsheet can grind your computer to a halt.
  • It’s Brittle: If someone adds or reorders a column in the lookup table, your formulas can break spectacularly.
  • It’s Inefficient: You end up with a massive table full of redundant data, which makes your file size balloon and complicates your analysis.
  • It Doesn’t Scale: What happens when you need to connect a third or fourth table? Another round of Lookups? It quickly becomes an unmanageable mess.

Creating relationships between your tables is the modern, more dynamic alternative. Instead of manually mashing tables together, you simply tell Excel how they’re related - for example, by a shared "Product ID" or "Customer ID." The tables remain separate and clean, but you can build a single Pivot Table that pulls fields from all of them as if they were one.

This method leverages a powerful feature running in the background of modern Excel: the Data Model. Let's see what that means.

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.

A Quick Intro to Excel’s Data Model

The "Data Model" might sound intimidating, but it's really just a backend engine in Excel that allows you to work with multiple tables simultaneously. It’s what powers many of Excel's more advanced data features, like Power Pivot and those relationship-driven Pivot Tables.

The best part? You don't need to be a data guru to use it. In fact, if you've ever created a Pivot Table from a table of data, you're just one checkbox away from using the Data Model. By simply telling Excel to "Add this data to the Data Model" when you create a Pivot Table, you unlock the ability to build connections between tables that were previously isolated in separate sheets.

Let's walk through a real-world example to see this in action.

Our Scenario: Analyzing Multi-Channel Sales Data

Imagine you run an e-commerce business. Your data is likely scattered across a few different places. For our example, let's say we have three separate tables of data:

  1. Sales Data: A list of every transaction, including the unique SaleID, the ProductID of the item sold, the CustomerID who bought it, the Date of the sale, and the total Revenue.
  2. Product Details: A lookup table with more information about each product, including the ProductID, ProductName, and Category (e.g., Apparel, Accessories, Homewares).
  3. Customer Details: A table containing customer information, with CustomerID and the State they live in.

Our goal is to create a Pivot Table that shows total revenue by product category, broken down by the customer's state. Without relationships, you'd be stuck doing multiple VLOOKUPs to pull 'Category' and 'State' into your main sales table. With relationships, we can do it in a matter of minutes.

Step-by-Step: Creating Relationships for Your Pivot Table

Follow these steps to connect our three tables and build a unified report.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

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

Watch Graphed demo video

Step 1: Format Your Data as Excel Tables

Before you do anything else, make sure each of your data sets has been formatted as an official Excel Table. This is crucial because tables have names and automatically expand as you add new data, meaning your Pivot Table will always have the latest information.

To do this for each of our data sets (Sales, Products, Customers):

  1. Click anywhere inside your data range.
  2. Press Ctrl + T on your keyboard (or go to Insert > Table).
  3. Excel will confirm the range. Make sure the "My table has headers" box is checked, then click OK.
  4. Give each table a clear, descriptive name. Click on your new table, go to 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 name ours 'Sales', 'Products', and 'Customers'.

Step 2: Create a Pivot Table and Add to the Data Model

Now we’ll create our Pivot Table, making sure to tick the all-important box that activates the Data Model.

  1. Click anywhere inside one of your tables - let's start with the 'Sales' table.
  2. Go to the Insert tab and click PivotTable.
  3. In the "Create PivotTable" dialog box, you’ll see your table name ('Sales') already selected.
  4. At the bottom of this dialog, there’s an option that says Add this data to the Data Model. Check this box. This is the key step!
  5. Click OK.

You'll now see a blank Pivot Table and its "PivotTable Fields" pane. At the top of the pane, notice the "All" tab is selected. This is where you’ll soon see all three of our tables, not just the one we started with.

Step 3: Connect Your Tables by Creating Relationships

Excel doesn't automatically know how these tables are connected. We need to tell it which columns to use to link them together. The primary link between our data is the transaction table, 'Sales'.

  1. On the Excel Ribbon, go to the Analyze tab (or PivotTable Analyze) and find the button named Relationships. Click it. (Alternatively, you can go to the Data tab > Data Tools > Relationships).
  2. The "Manage Relationships" dialog box will appear. Click New... to create your first connection.
  3. First Relationship (Sales to Products):
  • In the "Create Relationship" window, select the 'Sales' table from the first dropdown.
  • For the "Column (Foreign)," select ProductID. This is considered the "foreign key" because it refers to a key in another table.
  • For the "Related Table," select 'Products'.
  • For the "Related Column (Primary)," select ProductID. This is the "primary key" because each Product ID is unique in this table.
  • Click OK. You've just told Excel that for every ProductID in the 'Sales' table, there's a corresponding unique ProductID in the 'Products' table.
  1. Second Relationship (Sales to Customers):
  • Back in the "Manage Relationships" dialog, click New... again.
  • This time, select the 'Sales' table as the first table, and choose the CustomerID column.
  • For the "Related Table," select 'Customers'.
  • For the "Related Column," select the CustomerID column.
  • Click OK.
  1. You should now see both relationships listed in the "Manage Relationships" window. Click Close.

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.

Step 4: Build Your Multi-Table Pivot Table

This is the payoff. Go back to your PivotTable Fields pane. At the top, you should now see all three tables: 'Customers', 'Products', and 'Sales', each with a small cylinder icon. You can expand a table by clicking the arrow next to its name.

Let's build the report we wanted from the beginning:

  1. From the 'Sales' table, drag the Revenue field into the Values area.
  2. From the 'Products' table, drag the Category field into the Rows area.
  3. From the 'Customers' table, drag the State field into the Columns area.

Just like that, you have a single Pivot Table summarizing data from three separate tables! You have successfully analyzed revenue by product category and customer state without a single formula.

Best Practices and Pro Tips

  • Primary and Foreign Keys: The logic behind a relationship is linking a "many" side to a "one" side. In our example, the 'Sales' table can have many sales of the same product (many-side), but the 'Products' table should only have one row for each unique product ID (one-side). This "one-to-many" structure is fundamental to making relationships work correctly.
  • Keep Data Types Consistent: Make sure the columns you're using to link tables have the same data type. A 'ProductID' formatted as a number won't connect to a 'ProductID' formatted as text.
  • Use a "Diagram View" for a Visual Map: If you have the Power Pivot add-in enabled (you can enable it for free in Excel's options), you can go to the Power Pivot tab > Manage. Inside the Power Pivot window, click on Diagram View. This gives you a visual representation of your tables and the relationships between them, which is incredibly helpful when working with four or more tables.
  • DAX for Advanced Calculations: Once your data is in the Data Model, you can create much more powerful calculations using DAX (Data Analysis Expressions). These are like "super formulas" for Pivot Tables, allowing you to create metrics like year-over-year growth, customer lifetime value, and more.

Final Thoughts

Working with data across multiple tables is no longer something to fear in Excel. By leveraging the built-in Data Model, you can create clean, efficient, and powerful reports that combine information from different sources without the headache of VLOOKUPs or flattening data. It’s a game-changer for anyone doing serious analysis in spreadsheets.

Of course, there is still some manual work involved in formatting tables, creating relationships, and constructing the reports in Excel. At Graphed , we’ve found that even with these powerful tools, many marketing and sales teams still spend hours wrestling with data. That is why we built a tool that automates this entire process. You simply connect data sources like Google Analytics, Shopify, and Salesforce once. From there, you can just ask in plain English for the dashboard you need - like "Show me revenue by product category broken down by state from Shopify" - and our AI creates an interactive, real-time dashboard for you in seconds.

Related Articles