How to Create Many to Many Relationship in Excel

 

 

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.


How to Create Many to Many Relationships in Excel: Step-by-Step Procedures

Step 1 – Create a Table with a Dataset

  • We’ll create a dataset in the Cell range B4:D19 of a company’s Sales Report in October 2022.

Create Table with Dataset

  • Prepare another dataset for Profit Report.

  • Select the Cell range B4:D19 in the first worksheet.
  • Go to the Home tab and click on Format as Table.

  • Choose any style for the table.

  • You will get the dataset as a table like this.

  • Repeat the same procedure for the Profit Report worksheet.

Read More: How to Create a Relationship Between Tables in Excel


Step 2 – Add Tables to Power Pivot

  • Install the Power Pivot add-in to our workbook. Go to File and select Options.
  • Choose COM Add-ins under the Add-ins section and press Go.

Add Tables to Power Pivot

  • Select Microsoft Power Pivot for Excel and press OK.

  • Select the first worksheet.
  • Go to the Power Pivot tab in the Excel Ribbon and select Add to Data Model.

  • Do the same for the second worksheet.
  • You will get both tables in the Power Pivot window.

Read More: How to Create Data Model Relationships in Excel


Step 3 – Create a Pivot Table from Power Pivot

  • Select PivotTable from the Home tab in the Power Pivot window.

Create Pivot Table from Power Pivot

  • Choose PivotTable from the drop-down menu.

  • Choose the location of the Pivot Table. We chose New Worksheet.
  • Press OK.

  • Put the categories in respective fields under the PivotTable Fileds panel as shown below.

  • You will see that the Sum of Profit Amount is showing inaccurate values.

  • Go to the Diagram View in the Home tab of the Power Pivot window.

  • Connect the Date title of both tables by dragging the cursor from one to the other.

  • You will get a warning message which states that the relationship cannot be created due to duplicate values.

Read More: Create Entity Relationship Diagram from Excel


Step 4 – Produce a Date Table in Excel

  • Open a new worksheet with the Date values only.
  • Insert this formula in Cell C5 to get the Month value and press Enter.
=MONTH(B5)

Produce Date Table in Excel

Here, the MONTH function pulls out the month number from Cell B5 in a numeric format between 1 to 12.
  • Insert this formula in Cell D5 for getting the No. of Week for the respective date and hit Enter.
=WEEKNUM(B5)

In this formula, the WEEKNUM function returns the specific week number of the given date in Cell B5.
  • Insert this formula in Cell E5 for counting Weekdays and press Enter.
=WEEKDAY(B5,2)

The WEEKDAY function returns the specific day of the week from the reference Cell B5.
  • You will get details Date Table like this.

  • Apply the AutoFill tool to get individual parameters for all the dates.
  • Press Ctrl + T to convert the set into a table.

Read More: How to Create Relationship in Excel with Duplicate Values


Step 5 – Insert a Many-to-Many Relationship Between Tables

  • Add the Date Table to the Power Pivot following the process in Step 2.

Insert Many to Many Relationships Between Tables

  • Connect the Date titles of the Sales Report and Profit Report tables with the Date Table by clicking and dragging the cursor.

  • You will see the connection strings, which define that the tables have relationships.

Excel Many to Many Relationship

Read More: How to Make One to Many Relationship in Excel


Steps 6 – Get the Final Output in a Pivot Table

  • Go to the Pivot Table that was created earlier.
  • Drag Weekdays to the Rows field.

Get Final Output in Pivot Table

  • You will see that the Pivot Table is showing the accurate values.

  • Follow the same procedure for other categories as well.

Download the Practice Workbook


Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo