# 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.

## 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.

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

• Set your table range and press OK.

Thus, Table1 will be created.

• 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]))`

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

We can further modify our output based on our choice.

### 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.

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.

• 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.

Practice Section

For further expertise, you can practice here.

## 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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF