How to Manage Relationships in Excel (with Detailed Steps)

Relationships in Excel are formed when tables are combined by means of a column shared between them. In this article we will demonstrate how to create relationships in Excel, and then how to manage them, including editing, adding and deleting. We used the Microsoft Office 365 version here, but you can utilize any other version at your disposal.

Step 1: Prepare Dataset for Managing Relationships in Excel

First we’ll create the two tables between which we’ll create relationships. To produce the first table that looks like the image below:

Enter the main heading ‘Managing Relationships’ in some merged cells at a larger font size,
Enter the column headings: customer ID, Item Sold, Sales Process, Date, Quantity, Per Unit Price, and Total Price.
In the ID column, enter customer ID numbers sequentially.
In the Item sold column, enter the names of the items, and in the Sales Process column, the source of the sale.
Enter the Dates, Quantities and Prices per Unit.
Multiply the per unit price by the quantity to derive the values in the Total Price column.

After some formatting, the first table should look like this:

Prepare dataset to Manage Relationships in Excel

Prepare the second table containing the customer ID, Name, and Country like this:

Now we give the table a name, which we’ll use as a reference when creating relationships.

Select any cell randomly in the table range.
Go to the Table Design tab and type a name in the Product_Order field. Here, we named the table Product_Order.

Now we rename the second table.

Select any cell randomly from the table range.
Go to the Table Design tab and type a name in the Identity_1 field. We named the table Identity_1.

Step 2: Using Excel Pivot Table to Create Relationships

Our tables are ready. Now we create the relationships using a Pivot Table.

Go to the Insert tab, select Pivot Table, then From Table/Range.

The PivotTable form table or range window will appear.

Enter the Product_Order table in the Table/Range box.
Select New worksheet in the Choose where you want the PivotTable to be placed option.
Check the Add this data to the Data Model option.
Click on OK.

PivotTable Fields will appear in the new worksheet. We’ll create the Pivot table here.

Select the Name field from the Product_Order table and drag it to the Rows field. By clicking the arrow next to the table name, you can expand the table to see the fields.
Select Total Price from the Identity_1 table and drag it to the Values field.

The field list will be accompanied by a notice saying relationships may be needed between tables. If we choose Auto-Detect, Excel will create a relationship and if we choose the Create option, we will be able to create a table based on our preference.