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.

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.

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.

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.

Step 3 – Create a Pivot Table from Power Pivot

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

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.

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)

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.

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

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

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.

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.

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

Create Many to Many Relationship.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!