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 DatesQuantities 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.

Rename the Data Table

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.

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


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.

Create and Manage Relationships

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.

Select create option

  • To illustrate, select Auto-detect.

The Auto-Detect Relationships window appears.

  • Click on Close.

  • Select Create in the PivotTable Fields,

The Create Relationships window opens.

  • Select the Product_Order table in the Table option.
  • Select the Identity_1 table in the Related Table option.
  • Select ID in the Column (Foreign) option.
  • Select ID in the Related Column (Primary) option.
  • Click on OK.

Input data in the dialog box to manage relationships

The following Pivot Table is generated.

Read More: How to Create Data Model Relationships in Excel


Step 3: Applying Relationships Option to Manage Relationships in Excel

The relationships have been created. Now let’s see how to manage them.

  • Select any cell randomly in the table range.
  • Go to the Data tab and select the Relationships option from the Data Tools.

The Manage Relationships window will appear, where we can customize the relationships table as desired.

We could also create new relationships from the previously mentioned Product_Order and Identity_1 tables by clicking on the New option.

  • Choose the Edit option to modify the table of the current relationship.
  • Select the Delete option to delete the table of the current relationship.
  • Select the Deactivate option for customization purposes.
  • After completing the desired operations,  click on Close.

Edit and auto-detect to Manage Relationships

Read More: How to Create Many to Many Relationship in Excel


Download Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo