Sanjida Mehrun Guria
When we work with multiple data strings in different worksheets, we often need to merge them. It is mostly required to get an overall view of the whole workbook. To do this, Microsoft Excel has introduced Power Pivot to interlink all excel tables and the process is called many to many relationship. In this article, we will guide you on how to create many to many relationship in excel through some easy steps.
Download Practice Workbook
Download this sample file and practice by yourself.
What Is Many to Many Relationship in Excel?
When we interlink two or more tables based on one or more common items, then it is called the many to many relationship. It helps to fetch complicated information that has repetitive values in different scenarios.
People mostly adapt a many to many relationship for business forecasting, production control, analyzing decades of data, etc.
Types of Relationships Between Tables in Excel
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.
Step by Step Procedure to Create Many to Many Relationship in Excel
Now, we are at the main part of our article. Following we will discuss the process to create a many to many relationship between two tables with just 6 steps.
STEP 1: Create Table with Dataset
To start the process, we need to have datasets and convert them into excel tables.
- First, create a dataset in the Cell range B4:D19 of a company’s Sales Report in October 2022.
- Similarly, prepare another dataset of Profit Report.
- Now, select the Cell range B4:D19 in the first worksheet.
- Then, go to the Home tab and click on Format as Table.
- Afterward, choose any style for the table.
- Finally, you will get the dataset as a table like this.
- Along with it, follow the same procedure for the Profit Report worksheet as well.
Read More: How to Create a Relationship Between Tables in Excel (3 Ways)
STEP 2: Add Tables to Power Pivot
Now, we will insert the table into the Power Pivot. It is the ultimate analysis tool in excel for a dynamic data model. To do the task, follow the process below.
- Â In the beginning, we will install the Power Pivot add-in to our workbook.
- For this, go to the File > Options tab.
- Then, COM Add-ins under the Add-ins section and press Go.
- Next, select Microsoft Power Pivot for Excel and press OK.
- Now, select the first worksheet.
- Then, go to the Power Pivot table in the Excel Ribbon and select Add to Data Model.
- Along with it, do the same for the second worksheet as well.
- Finally, you will get both tables in the Power Pivot window.
Read More: How to Create Data Model Relationships in Excel (3 Ways)
STEP 3: Create Pivot Table from Power Pivot
At this stage, we will create a Pivot Table to get summarized data from the Power Pivot. Let’s see how it works.
- Firstly, select PivotTable from the Home tab in the Power Pivot window.
- Secondly, choose PivotTable from the drop-down menu.
- In the following step, choose the location of the Pivot Table.
- As we want it in another worksheet, that is why we choose New Worksheet.
- Afterward, press OK.
- Next, put the categories in respective fields under the PivotTable Fileds panel as shown below.
- Afterward, you will see that Sum of Profit Amount is showing inaccurate values.
- To find the reason, go to the Diagram View in the Home tab of the Power Pivot window.
- Here, connect the Date title of both tables by dragging the cursor all over them like this.
- Lastly, you will get this warning message which states that the relationship cannot be created due to duplicate values.
Read More: Create Entity Relationship Diagram from Excel (with Quick Steps)
STEP 4: Produce Date Table in Excel
This error occurs because both tables have duplicate values like Date and Product. As they are interrelated yet, we will create a separate Date table to solve this.
- In the beginning, open a new worksheet with the Date values only.
- Then, insert this formula in Cell C5 to get the Month value and press Enter.
=MONTH(B5)
- Then, insert this formula in Cell D5 for getting the No. of Week for the respective date and hit Enter.
=WEEKNUM(B5)
- Similarly, type this formula in Cell E5 for counting Weekdays and press Enter.
=WEEKDAY(B5,2)
- Finally, you will get details Date Table like this.
- Lastly, apply the AutoFill tool to get individual parameters for all the dates.
- Also, press Ctrl + T to convert it into a table.
Read More: How to Create Relationship in Excel with Duplicate Values
STEP 5: Insert Many to Many Relationships Between Tables
Finally, we are at the stage, where we will create the many to many relationship between the tables. To do this, follow the process below.
- In the beginning, add the Date Table to the Power Pivot following the process in Step 2.
- Then, connect the Date titles of the Sales Report and Profit Report tables with the Date Table by dragging the cursor like this.
- Finally, you will see the connection strings which define that the tables have many to many relationship.
Read More: How to Make One to Many Relationship in Excel (2 Simple Ways)
STEP 6: Get Final Output in Pivot Table
To view the final output, follow the process below.
- Initially, go to the Pivot Table that we created earlier.
- Then, drag Weekdays to the Rows field.
- Finally, you will see that the Pivot Table is showing the accurate values which means we have successfully created many to many relationship among the tables.
- Follow the same procedure for other categories as well.
Conclusion
Henceforth, we are concluding our article on how to create many to many relationship in excel. Here we described the process with easy steps for your better understanding. Let us know your insightful suggestion on this. Follow ExcelDemy for more articles like this.
