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
- One to One: Only a single record is related between two tables.
- One to Many: A single record of one table is connected to multiple records of another table.
- 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.
- First of all, organise 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.
- 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.
You can modify your output based on your preferences.
For further expertise, you can practice here.
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.