Create a Relationship Between Two Tables
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
If the tables in your data source do not have existing relationships, or if you add new tables, you can use the tools in PowerPivot for Excel to create new relationships. For information about how relationships are used in PowerPivot for Excel, see Relationships Overview.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
To create a relationship between two tables
In the PowerPivot window, click the Design tab, and in the Relationships group, click Create Relationship.
In the Create Relationship dialog box, click the down arrow for Table, and select a table from the dropdown list.
In a "one-to-many" relationship, this table should be on the "many" side.
For Column, select the column that contains the data that is related to Related Lookup Column.
For Related Lookup Table, select a table that has at least one column of data that is related to the table you just selected for Table.
In a "one-to-many" relationship, this table should be on the "one" side, meaning that the values in the selected column do not contain duplicates. If you attempt to create the relationship in the wrong order (one-to-many instead of many-to-one), an icon will appear next to the Related Lookup Column field. Reverse the order to create a valid relationship.
For Related Lookup Column, select a column that has unique values that match the values in the column you selected for Column.
Click Create.
To create a relationship in Diagram View
In Diagram View, you can easily create relationships between columns in separate tables. The relationships appear visually, which enables you to quickly see how all the tables relate to each other. For more information about Diagram View, see PowerPivot Window: Diagram View.
You can create a relationship by dragging a column in one table to a related column in a different table. You can also follow the steps in this section to create a relationship using the Create Relationship dialog.
To navigate Diagram View
In the PowerPivot window, on the Home tab, in the View area, click Diagram View. The Data View spreadsheet layout changes to a visual diagram layout, and the tables are automatically organized, based on their relationships.
To see all the tables on the screen, click the Fit to Screen icon in the top-right corner of Diagram View.
To organize a comfortable view, use the Drag to Zoom control, the Minimap, and drag the tables into the layout that you prefer. You can also use the scroll bars and your mouse wheel to scroll the screen.
Point to a relationship line (black line with an arrow and circle on the ends) to highlight the related tables.
To create a relationship between tables in Diagram View
While still in Diagram View, right-click a table diagram, and then click Create Relationship. The Create Relationship dialog box opens.
For Column, select the column that contains the data that is related to Related Lookup Column.
For Related Lookup Table, select a table that has at least one column of data that is related to the table you just selected for Table.
Click Create. An inactive relationship appears as a dotted line. (The relationship is inactive because an indirect relationship already exists between the two columns.)
Point to the relationship line between the tables.
Right-click the line, and then click Mark as Active.
Note
You can only activate the relationship if no other relationship relates the two tables. If tables are already related, but you want to change how they are related, you must mark the current relationship as inactive, and then activate the new one.
Considerations for Creating Relationships
When creating a relationship, consider the following:
There can be only one relationship between each pair of tables.
The data values in Related Lookup Column must be unique. In other words, the column cannot contain duplicates.
Each table must have a single column that uniquely identifies each row in that table.
The data types in Column and Related Lookup Column must be compatible. For more information about data types, see Data Types Supported in PowerPivot Workbooks.
For a complete list of requirements, see "Requirements for Relationships" in Relationships Overview.