How to create a relationship between two tables in Excel?
Table of Contents
What does “table relationship” mean in Excel?
First, let’s define what a table relationship is, per Microsoft’s documentation, the official definition is:
A relationship is a connection between two tables that contain data: one column in each table is the basis for the relationship.
Let’s suppose we have two tables:
- Stock Report – a report we receive from the supplier that performs physical count of stock in all warehouses.
- Product List Price and Cost – a catalog of all products with their selling price and production cost.
Stock report:
Product list and cost:
In the above Stock Report – Product List example, the connection between the two tables is the “Product” column – because it’s the column that both tables have in common and the column that would help us “bring” the price and cost columns into the stock report.
Why relationships are essential when working with datasets?
In real life, data rarely exists in one big table. It’s often separated into separate tables to ensure a structured method of storing data. Tables usually classify into two main categories:
- Catalog or Dimension Tables – as the name suggests, these are tables we use as “catalogs” – such customers, products, regions.
- Transactional Tables – this is where we track all transactions that happen in the system – sales, purchase orders, invoices.
Even if table relationship is a concept for databases, we can still apply it when working in Microsoft Excel. Similar to when we want to create a “vlookup” or “xlookup” function – we need to find what’s the column that we can use to find the related value in a separate table.
But Excel has a very important module that can be used to create an actual Data Model that goes beyond flat tables and vlookups: Power Pivot.
Power Pivot: Data Models in Microsoft Excel
The official Power Pivot definition from Microsoft’s website is as follows:
Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.
Prerequisites before creating relationships
Use Excel tables instead of ranges
How does Power Automate recognize website elements?
Ensure Your data has unique keys
For the Product List for example, we would need to make sure the column “Product” doesn’t contain any duplicate. You can do this by either creating a formatting rule or checking through a pivot table.
Option 1: Use conditional formatting
Option 2: Create pivot table from the data and check if any row shows a count greater than 1
Activate Power Pivot in Excel
Go to File > Options > Add-Ins.
In the Manage box, click COM Add-ins> Go.
Check the Microsoft Office Power Pivot box, and then click OK.
Power Pivot should show:
Step-by-Step: How to Create a Relationship Between Two Tables in Exce
Step 1 – Connect to your data source with Power Query
Power Query is Excel’s ETL (Extract-Transform-Load) tool, and it’s Power Pivot’s best friend to connect to multiple data sources such as other files in the local computer, files in SharePoint, SQL Databases or even Dataverse.
The data extraction step might change depending on the data source that will be used. We have many videos on this topic in our YouTube channel, make sure to check them out!
Option 1 – Connect to different database types
Example 2 – Connect to other files in the local PC
This is how all the connections should look once set up:
Step 2 – Load tables into the Data Model
Right click on the query and then click on the “Load to” option
This will make below pop-up to show up. Check the “Add this data to the Data Model” checkbox and then hit “ok”.
Step 3 – Open Power Pivot and manage relationships
Go to Power Pivot tab, and then Manage.
This will open the Power Pivot Interface. You will see a tab for all the tables added to the data model.
Step 4 – Create the relationship based on keys
Go to Diagram View
Drag and drop the 2 columns that are related and Power Pivot will create the relationship accordingly
Step 5 – Confirm and use in PivotTables or reports
Click on “Pivot Table”
Confirm where the Pivot table should be created in:
You’re ready to start slicing and dicing data with a Pivot Table that connects multiple tables:
Measures using DAX can be added to the data model. If you are already familiar with Power BI, Power Pivot uses the same type of expressions to perform calculations (with some few exceptions).
Common errors and how to troubleshoot them
“The relationship cannot be created because each column contains duplicate values.”
This error happens when a catalog table has duplicate values. In our products list table, this error could have shown up if one of the products was listed twice. To fix it, find the duplicate value and remove it from the list.
To check for duplicates you can use some of our recommendations listed above (Use conditional formatting or create pivot table from the data and check if any row shows a count greater than 1)
Issues with formatting or data types
Always make sure your keys have the same data type. If a key column is created as Text in the catalog table (products), it should be the very same data type in the transactional table (stock report).
Same if we’re using numeric IDs for key columns – which is usually the most common case for many databases. Make sure they are set as integers on both sides of the relationship.
How relationships in Excel connect with Business Automation
Excel as a foundation for automation
Relationships in Excel when created through Power Pivot, create structured, connected data models—almost like in databases. It’s important to emphasize that Excel is not a database, but at the same time it’s no secret that almost all teams across organizations use Excel in their day-to-day tasks.
Having a data model structure makes Excel a powerful foundation for automation – it also helps with creating more optimized files. When data is properly related, it becomes easier to create reports and to bring bigger volumes of data into the file.
When Excel is combined with Power Query and Power Pivot, it enables developers or end users to build templates for recurring tasks, creating financial models, or inventory tracking systems that refresh automatically when source data changes.
In this way, Excel becomes a reliable tool for automating routine and repetitive tasks.
Streamlining Data Workflows with PowerGI’s Digital & Robotic Process Automation
Excel is such a universal tool, while it should be avoided to store data when we’re creating new systems, but again, it’s widely used by almost all companies – so the need to automate tasks around it arises very often.
For example, it’s common to receive an Excel file, apply transformations (such as calculated columns or removing existing columns) and finally try to convert the content into CSV format to upload it to an external system. This type of automation can be achieved by both Macros in Excel and Office Scripts and can be integrated into a bigger automation orchestrated by Power Automate.
We have partnered with a retail company from Colombia to create a template that automatically grabs information from over 1 million records hosted in multiple Excel files downloaded from an external system and creates a summary of unmatched transactions. This task took around 5 or 6 hours every week, but after the Power Query-Power Pivot automation (along with some pretty cool macros), it only takes the accounting team a single click and around 15 minutes of manual review of a few transactions.
When to Use Excel vs. a Full BI or automation solution
“The dashboard looks great, but can I get this data in Excel format?”.
We’re pretty sure we’ve all heard this when presenting a new dashboard to an end user. All use cases are different and the decision to either go with Excel or a tool like Power BI depends on many factors.
Here are a few thoughts to make this decision:
- MS Excel i’s perfect for individual analysts or small teams who need to explore data or generate ad-hoc reports.
- If users need to slice and dice data and drill down to the lowest detail available of the data, MS Excel is a good option
- If reporting becomes repetitive, collaborative, or mission-critical, a reporting tool like Power BI can become really useful.
- As data volume grows it’s worth considering a tool like Power BI volume. MS Excel has a limit on the number of rows that fit a tab. User’s computers also have a limit on the size of files that can be handled.
- When leadership required high-level summaries, a reporting tool like Power BI can become really useful.
Frequently Asked Questions (FAQs)
Can you relate more than two tables in Excel?
Yes, more than two tables can be added to the data model and connected between them. A single transactional table can be connected to one or many catalog tables. For example, the sales table can have relationships with tables such as:
- Customer
- Sales Representative
- Dates List
Is Power Pivot required for creating relationships?
Yes. With xlookups or vlookups, you can bring a column from another table but this is not the same as actually relating two tables and creating a connection between them like what we do from Power Pivot.
Can relationships be created without unique keys?
No, it’s a requirement to at least one side of the relationship to have unique keys. You can review our suggestions on how to spot duplicate values in catalog tables to ensure your data meets this requirement.
