Create Relationships in Excel (3 Effective Ways)

In this Excel tutorial, you will learn how to create relationships. In the first method, we will use PivotTable to create relationships between tables. Moreover, you will learn to create relationships using Power Pivot. In the last one, we will create a relationship between 2 tables manually.

We have used Microsoft 365 to prepare this article. But you can use these methods in Excel versions from Excel 2003 onwards.

In Excel, we create relationships between tables that are logically related to each other. It helps us to avoid data redundancy as well as fetching data from multiple tables simultaneously.

Download Practice Workbook

Create Relationships.xlsx

What Are the Types of Data Relationships?

There are mainly 3 types of relationships by which we can connect tables. They are:

One-to-One Relationship: Each record in a table connects with exactly one record in another table. In a company, an employee normally has only one manager. This relationship between employee and manager is a one-to-one relationship.
One-to-Many Relationship: It refers to a connection between two tables where each record in the first table has a connection with multiple records in the second table. But, all the records in the second table can have only one connection with the first table. An ideal example is a teacher teaching in a class. The relationship between the teacher and students is an example of a one-to-many relationship.
Many-to-Many Relationship: Suppose, the records in the first table are connected to more than one record in the second table and vice versa then it is a many-to-many relationship. The most common many-to-many relationship is the relationship between products and customers in a grocery shop. A customer may purchase more than one product. Also, multiple customers may buy the same product. So, the relationship between them is a many-to-many relationship.

How Many Methods Are Available to Create Relationships in Excel?

There are three main ways by which you can create relationships in Excel.

1. How to Create Relationships Using PivotTable?

We have 2 tables in our worksheet. The first one contains information about customers and the second one is regarding their purchase information. In the very first method, we will use PivotTable to create relationships between these 2 tables.

First, convert your dataset to a table by pressing Ctrl+T.
Right after that, go to the Design option in the Quick Access Toolbar. Under the blank space of the Table Name: tab, assign a name to your table like the following image.

Now, we will introduce PivotTable into our worksheet. Click on the Insert tab and then on PivotTable.

At this point, a dialogue box will appear in your screen. Choose the options like the image below in the box and click OK.

In the same way, add another table to the data model. The other table looks like the following image.

Now, we will work on the PivotTable Fields window. Select the option All in the upper section of the window.

Later, we will drag 2 columns from two tables; one under the Rows area and another under the Values area.

Soon after that, a user prompt will appear, and pick CREATE from there.

Following that, a user interaction box will appear. In the Table: we choose our 1st table Product and in the Related Table: we select the second table Customer 1. In both the table common column was Customer ID, so we choose it for Primary as well as Foreign Column.

Doing all these, you will find a relationship created between 2 tables like the image below.

2. Can I Build Relationships in Excel Using Power Pivot?

In order to build relationships in Power Pivot, you will have to bring the Power Pivot ribbon from Excel Add-ins. Do the following things to know how to bring the Power Pivot option in Excel.

Go to Developer >> COM Add-ins.

Click here to enlarge the image

A new dialog box may appear. Check the proper option in the box and click OK.

In the first step of using Power Pivot to create relationships, you will have to add tables in the Data Model like the following image.

Similarly, add the other tables here in the same way. If you click on the Power Pivot for Excel window, you will see that tables are added here.

Now, drag and drop the unique field identifier from one table to another according to your need. Finally, You will find the window looking like the image below.

At this point, we will see the output of the relationship we have already created. Follow the image below to create a PivotTable in your worksheet.

Now, drag and drop the fields you want to see in the PivotTable. You can see the procedure in the gif below.

Under the Values area, add “Price” in the same process. You will find a PivotTable is created in the worksheet.

3. Is There Any Way to Create Relationships Between Tables Manually?

Creating relationships manually is the easiest among all the methods. We do have 2 tables in our dataset named “Product” and “Customer”. We will create a new column named “Name” and write the formula in the first cell.

Hit ENTER and finally use the AutoFill feature to fill the remaining cells.

Which Things You Have to Keep in Mind?

Analyze the structure of your data and identify common fields, since you will create relationships based on the matching values in the common fields.
Excel supports different types of relationships. Choose the appropriate one for your dataset.
When you create relationships with large datasets, it may slow down the performance. In that case, you can use advanced tools like Power Pivot or a relational database to boost your performance.

Frequently Asked Questions

1. Can I establish relationships between tables from different worksheets or workbooks?

Answer: Yes, you can. However, ensure that the desired worksheets or workbooks are accessible as well as they have at least one common field or column.

2. Are there any alternatives to creating relationships in Excel for managing related data?

Answer: There are multiple alternatives to creating relationships in Excel. You may use the combination of VLOOKUP and HLOOKUP functions. Or, you may use INDEX and MATCH functions together. Moreover, you can use Power Query or Database Management System (DBMS) in lieu of creating relationships.

3. Are there any performance implications when working with large datasets and complex relationships in Excel?

Answer: Large datasets and complex relationships require large memory and high processing power. So, when you work with this type of dataset, keep it in mind and be careful about your available resources.

Conclusion

After going through this article, you have learned how to create relationships in Excel. You have found 3 different methods to do this task. If you don’t want to change any settings of Excel, then the PivotTable feature is perfect for you. However, if you can bring some changes to the default settings of Excel, then you may use the Power Pivot feature. Otherwise, you can also create relationships manually following the last method. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries. Finally, please visit our site for more exciting articles on Excel.

Create Relationships in Excel: Knowledge Hub

<< Go Back to Data Model in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!