Delete relationships between tables in a Data Model

Applies To
Excel for Microsoft 365 Excel 2024 Excel 2021

Tables in a Data Model can have multiple relationships. This situation often occurs if you purposely create extra relationships between tables that are already related, or if you import tables that already have multiple relationships defined in the original data source.

While multiple relationships can exist, only one relationship is active at a time and provides the primary data navigation and calculation path. Any extra relationships between a pair of tables are considered inactive.

You can delete existing relationships between tables if you're sure they're not needed, but be aware that this action can cause errors in PivotTables or in formulas that reference these tables. PivotTable results can change after a relationship is deleted or deactivated.

Start in Excel

  1. Select Data > Relationships.
  2. In the Manage Relationships dialog box, select one relationship from the list.
  3. Select Delete.
  4. In the warning dialog box, verify that you want to delete the relationship, and then select OK.
  5. In the Manage Relationships dialog box, select Close.

Start in Power Pivot

  1. Select Home > Diagram View.
  2. Right-click a relationship line that connects two tables and then select Delete. To select multiple relationships, hold down CTRL while you select each relationship.
  3. In the warning dialog box, verify that you want to delete the relationship, and then select OK.

Note

  • Relationships exist in a Data Model. Excel also creates a Data Model when you import multiple tables or create relationships between tables. You can purposely create a Data Model to use as the basis for PivotTables and PivotChart reports. For more information, see Create a Data Model in Excel.
  • You can roll back a deletion in Excel if you close Manage Relationships and select Undo. If you're deleting the relationship in Power Pivot, there's no way to undo the deletion of a relationship. You can re-create the relationship, but this action requires a complete recalculation of formulas in the workbook. Therefore, always check first before deleting a relationship that is used in formulas. See Relationships between tables in a Data Model for details.
  • The Data Analysis Expressions (DAX) RELATED function uses the relationships between tables to look up related values in another table. It returns different results after the relationship is deleted. For more information, see the RELATED Function.
  • In addition to changing PivotTable and formula results, both the creation and deletion of relationships cause the workbook to recalculate, which can take some time.