Delete relationships between tables in a data model

Tables in a data model can have multiple relationships. This is likely to be the case if you purposely create additional 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 exist, only a single relationship serves as the active, current relationship that provides the data navigation and calculation path. Any additional relationships between a pair of tables are considered inactive.

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

Start in Excel

1. Click Data > Relationships.

2. In the Manage Relationships dialog box, select one relationship from the list.

3. Click Delete.

4. In the warning dialog box, verify that you want to delete the relationship, and then click OK.

5. In the Manage Relationships dialog box, click Close.

Start in Power Pivot

1. Click Home > Diagram View.

2. Right-click a relationship line that connects two tables and then click Delete. To select multiple relationships, hold down CTRL while you click each relationship.

3. In the warning dialog box, verify that you want to delete the relationship, and then click OK.

   Notes: 

   • Relationships exist in a Data Model. Excel also creates a model when you import multiple tables or relate multiple tables. You can purposely create a Data Model to use as the basis for PivotTables, PivotCharts, and Power View 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 click Undo. If you're deleting the relationship in Power Pivot, there is 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 Overview for details.

   • The Data Analysis Expression (DAX) RELATED function uses the relationships between tables to look up related values in another table. It will return 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 will cause the workbook to be recalculated, which can take some time.

Top of Page