Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Make One to Many Relationship in Excel (2 Simple Ways)

In terms of working with Microsoft Excel, we can create a co-relation between various data. We can merge them or even create duplicate data with them. There are many features in Excel that can be used to manage relationships between data points. The Relationship feature is one such feature that can be used to create a connection between two data points. This relationship can be used to create a function that can be used to calculate the value of one data point based on the value of another data point. In Excel, we can create three different types of relationships among tables. They are

  1. One to One: Only a single record is related between two tables.
  2. One to Many: A single record of one table is connected to multiple records of another table.
  3. Many to Many: Multiple records of each table are related to each other.

In this article, we will try to explain two simple ways how to make one to many relationship in Excel. I hope this article will be helpful for you if you are looking for a simple and effective way to make one to many relationship.


Download Practice Workbook


2 Simple Ways to Make One to Many Relationship in Excel

There are many ways to make one to many relationship in Excel. Two of them I am going to discuss in the below section.


1. Make One to Many Relationship with Formula

We can simply make one to many relationship in Excel with formula. The whole procedure is described below.

Steps:

  • First of all, organise two co-related datasets.

 Excel One to Many Relationship

  • Now, form tables with both datasets.
  • Select the entire first dataset.
  • Then, go to the Insert tab and click on Table from the ribbon.

Make One to Many Relationship with Formula

  • Set your table range and press OK.

Thus, Table1 will be created.

 Excel One to Many Relationship

  • Use the same procedures and make Table2.

  • Now, select a cell (i.e. K5) and input the following formula to have the average CGPA of all the departments.
=AVERAGE(IF(COUNTIFS(Table2[Std ID],Table1[Std ID],Table2[Department],J5),Table1[CGPA]))

 Excel One to Many Relationship

  • Afterward, press ENTER to have the average CGPA of all the departments.

We can further modify our output based on our choice.

 Excel One to Many Relationship

Read More: How to Create Many to Many Relationship in Excel


2. Make One to Many Relationship with PivotTable

Another smart way to make one to many relationship in Excel is the use of PivotTable. The steps are described in the following section.

Steps:

  • Go to the Power Pivot tab first.
  • Then, click on Manage from the ribbon.

Make One to Many Relationship with PivotTable

The Power Pivot for Excel window will appear.

  • Afterward, go to PivotTable from the ribbon under the File tab.
  • From the available options, select PivotTable.

  • Pick your PivotTable location and click on OK.

Excel One to Many Relationship

  • Now, from the PivotTable Fields, manage your PivotTable. Here, I have set Department as Rows and Sum of CGPA as values.

Thus, we will have the sum of CGPA for all the departments.

  • Now, right-click on the Sum of CGPA title.
  • Pick Average from the Summarize Values By group.

Now, we will have the average CGPA for all the departments.

Excel One to Many Relationship

You can modify your output based on your preferences.

Excel One to Many Relationship

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


Practice Section

For further expertise, you can practice here.

Practice section


Conclusion

At the end of this article, I like to add that I have tried to explain two simple ways on how to make one to many relationship in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo