Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×