How to Create a Relationship Between Tables in Excel (3 Ways)

Sometimes, we may need a little amount of information from a large set of a data table. In this case, creating a relationship between the tables is of great advantage as it makes the data table reader-friendly by organizing data in a fluent manner. In this article, I will show you how to create a relationship between tables in Excel.


Download Practice Workbook

You can download the practice book below.


3 Effective Ways to Create a Relationship Between Tables in Excel

In this section, you will find 3 effective and suitable ways to create a relationship between tables in Excel. I will demonstrate them here one by one. So, let’s check them right now!


1. Using Pivot Table

Let’s say, we have two tables describing different products ordered by some customers from separate addresses and their respective prices.

I have created the first column named Customer Information with headings: Customer ID, Name, and Address.

Create Relationship Between Tables in Excel

Another table named Order Information has headings like: Name, Product, and Price.

Create Relationship Between Tables in Excel

It is noticeable that there must be a common column to create a relationship between the tables.

Here, I will show the method of creating relationships using Pivot Table. In order to demonstrate this method, proceed with the following steps.

Steps:

  • First of all, select the data range of the first table (i. e. Customer Information)> go to the Insert tab> click Table.

Create Relationship Between Tables in Excel

  • Then, click OK on the Create Table dialogue box.

Create Relationship Between Tables in Excel

  • Now, your table will be created.

Create Relationship Between Tables in Excel

  • Here, follow the same procedure for the other table (i.e. Order Information).

Create Relationship Between Tables in Excel

  • Now, click on the Table > go to the Table Design tab> assign a name for the table.

I have named the first table Customer and the second table Order.

Create Relationship Between Tables in Excel

  • After that, click on the Customer table> go to the Insert tab> and click Pivot Table.

  • Here, PivotTable from table or range dialogue box will show up. Mark  Add this data to the Data Model box and click OK.

  • Now, the Pivot Table Fields will show up.

  • Now, select the data type from both the tables (i.e. Address from the first table and Price from the second table) and click CREATE.

  • After that, Create Relationship dialogue box will show up. Here, assign the table names and the common column (i.e. Name) and click OK.

  • Finally, Excel will create a relationship between the two tables.

Create Relationship Between Tables in Excel

So, these are the steps you can follow to create a relationship between tables using the Pivot Table option.


2. Create Relationship Manually

You can also create relationships between tables manually. For the same set of data, we will now create a relationship manually. In order to do so, just proceed with the steps below.

Steps

  • First of all, add a new column to a table with the heading of another table that you want to relate to this table. (in our second table, there is a column named Price. We want to relate this to the first table).

  • Then, select a cell in the new column, and apply the following formula.
=Order[@Price] 

This will be done by just putting a “=” sign and then selecting a value of the Price column from the second table.

  • As a result, a relationship will be created between these tables.


3. Create Relationship Between Tables From Data Tab

You can also create a relationship between tables by using the Relationship option from the Data tab. For this, follow the steps below:

  • First of all, Create Table by following the procedure stated in method 1.
  • After creating the table, go to the Data tab> select Data Tools. You will see that the Relationship option has been activated. Click it.

  • Now, the Create Relationship dialogue box will show up just like method 1. Here, assign the Table name and the common Column name. Click OK.

  • Hence, you will see that your relationship has been created.


Conclusion

In this article, I have tried to show you some methods of how to create a relationship between tables in Excel. I hope this article has shed some light on your way of creating a relationship between tables in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo