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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Make One to Many Relationship in Excel: 2 Simple Ways

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, organize 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


Practice Section

For further expertise, you can practice here.

Practice section


Download Practice Workbook


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo