How to Use Excel Data Modeling to Create Relationships Between Tables

For decades, the undisputed king of Excel functions has been VLOOKUP (and more recently, XLOOKUP). If you have a massive “Sales” table containing a “Customer ID,” and you need to bring in the customer’s actual name from a separate “Customers” table, you write a VLOOKUP formula to pull that data across. But what if your workbook contains a Sales table, a Customers table, a Products table, and a Regions table? Writing thousands of VLOOKUP formulas to merge all this data into one giant flat sheet creates massive file sizes and slows calculation times to a crawl. The modern, professional solution is to use the Excel Data Model to build a relational database directly inside your spreadsheet.

What is the Data Model?

The Data Model is the invisible engine powering Power Pivot. It allows you to load millions of rows of data into Excel’s memory (bypassing the 1,048,576 row limit) and link tables together using relationships, exactly like Microsoft Access or SQL Server. Once linked, you can build a single PivotTable that instantly analyzes data from all the interconnected tables, without a single VLOOKUP required.

Step 1: Format as Tables

Before you can link data, it must be formatted as official Excel Tables.

1. Click inside your raw Sales data.
2. Press Ctrl + T and click OK.
3. Immediately go to the Table Design tab on the ribbon and change the “Table Name” on the far left to something logical, like tblSales.
4. Repeat this process for your Customers data (name it tblCustomers) and Products data (tblProducts).

Step 2: Add Tables to the Data Model

Now you must push these tables into the invisible backend engine.

1. Click anywhere inside tblSales.
2. Navigate to the Power Pivot tab on the ribbon. (If you don’t see this tab, go to File > Options > Add-ins > Manage: COM Add-ins, and enable Microsoft Power Pivot).
3. Click the Add to Data Model button.
4. A new window (the Power Pivot editor) will open, showing a green tab for your table. Close this window for now.
5. Repeat this process to add tblCustomers and tblProducts to the Data Model.

Step 3: Create the Relationships (The Visual Method)

Now we must tell Excel how these tables are connected.

1. On the normal Excel ribbon, go to the Data tab.
2. In the “Data Tools” group, click the Relationships button.
3. While you can click “New” here and use dropdowns, the visual method is much better. Close the dialog box.
4. Go to the Power Pivot tab and click Manage to open the backend editor.
5. In the Home tab of the Power Pivot window, look at the “View” section on the far right and click Diagram View.

You will now see your three tables represented as floating boxes containing lists of their columns. This is your database schema.

Step 4: Drag and Drop to Link

To connect the tables, you simply drag a line between the matching columns (keys).

1. Find the CustomerID column in the tblSales box.
2. Click and hold it, then drag your mouse over to the CustomerID column in the tblCustomers box, and release.
3. A line will appear connecting the two. You have just established a One-to-Many relationship (one customer can have many sales).
4. Repeat the process: Drag ProductID from tblSales to the ProductID column in tblProducts.

Your tables are now permanently linked in memory. Close the Power Pivot window.

Step 5: Build a Multi-Table PivotTable

Now for the payoff. You can build a PivotTable that seamlessly aggregates data across all three tables.

1. In the standard Excel window, go to the Insert tab and click PivotTable.
2. Instead of selecting a range, choose From Data Model.
3. Click OK to place the PivotTable on a new sheet.

Look at the PivotTable Fields pane on the right. Instead of seeing columns from one table, you will see all three of your tables (Sales, Customers, Products) listed with little database icons next to them.

• Expand the Products table and drag “Category” into the Rows box.
• Expand the Customers table and drag “State” into the Columns box.
• Expand the Sales table and drag “Total Revenue” into the Values box.

You have just created a matrix showing revenue by product category and customer state, drawing data from three entirely separate tables, calculating in milliseconds, with zero VLOOKUPs slowing down your workbook.