In Microsoft Excel, you can create relationships between two tables based on the matching data within the table, even when the tables are from different sources. In older versions of Excel, people have used functions such as VLOOKUP, INDEX, and MATCH to create relationships between tables, but in newer versions of Excel, there is a Relationship command that you can use to create relationships between tables in Excel easily.
How to create relationships between tables in Excel
Follow the steps below to create relationships between tables in Excel.
Naming the Tables
- When creating a relationship between tables, the tables must be related.
- Now we are going to name both tables.
- Select the table you want to give a name to and click the Table Design tab.
- On the Table Design tab, type a name for the table in the Table Name box in the Properties group. Do the same for the other table.
Creating Relationships between tables
Now we will create the relationship between tables after naming the tables.
On the Data tab, click Relationships in the Data Tools group.
A Manage Relationships dialog box will open.
In the Manage Relationship dialog box, click the New button.
In the Create Relationship dialog box, click the drop-down arrow for the table and select a table from the list.
Now click the drop-down arrow for the Related Table and choose the related table from the list.
In the Column (Foreign) list box, select the column that is related in both tables, for example, Customer ID.
In the Related Column (Primary), select the column Customer ID because it is in both tables.
Then click OK.
Then click Close.
Using the Pivot table as a lookup
Open a new worksheet.
Select a cell, for example, cell A1.
Click the Insert tab, and click the Pivot Table button.
Now select the option From Data Model.
A PivotTable from Data Model dialog box will open.
Click the option Existing Worksheet, then click OK.
The PivotTable Fields pane will appear on the right.
Click the drop-down arrow of the table you want to use in the lookup. In this tutorial, we have selected the Customer ID field from the Order table.
We will check the check box for the Customer ID field and drag it to the Rows area in the pane.
Try to drag fields from both tables into the Rows Area.
If the table includes a price, cost, or total, drag it into the Values area.
The results will appear on the left.
We hope you have understood how to create relationships between tables in Excel.
Which key creates relationship between two tables?
When it comes to the Foreign key in Microsoft Excel, it helps to determine the relationship between tables. The Foreign key helps to connect multiple tables in a database or Excel spreadsheet. The Foreign key is to be a column that is in both tables.
What are table relationships?
A table relationship connects tables and matches data in both tables. Relationships use key fields, which are fields with the same name and data in both tables. In Microsoft Excel, the Foreign key is the primary key that lets users match data in both tables.
