How to Link Tables in Excel & Google Sheets

This tutorial demonstrates how to link tables in Excel and Google Sheets.

 

 

Create Relationship Between Tables

If you have two or more tables in Excel that have a common field name (column heading), you can link them by forming a relationship between the tables.

1. In the Ribbon, go to Data > Data Tools > Relationships.

 

 

2. In the Manage Relationships box, click New…

 

3. In the Create Relationship box, from the Table drop down on the left, select the table that has multiple entries of the common field (ie Customer ID),  and then select the Related Table which would only have one entry each of the common field.

 

 

4. In the Column (Foreign) drop down on the right, choose the common field name (eg Customer ID).   In this example, in Table 1, the Customer ID can appear multiple times as each customer can place an order multiple times.

 

 

5. In the Related Column (Primary) drop down, choose the common field once again.   This time, the common field is Primary which means that the value in the field (Customer ID) can only appear once.  Customer Details are not repeated in this table – a unique Customer ID is stored in each row.
6. Click OK to create the relationship.

 

 

7. The new relationship shows up in the Manage Relationships dialog box.

8. Click Close.

Create Pivot Table From Multiple Tables

To create a pivot table out of multiple tables, use the Data Model feature.

1. In the Ribbon, go to Insert > Pivot Table > From Data Model.

 

 

2. Select New Worksheet and click OK.
3. Both tables show up in the PivotTable Fields list, and fields from both tables can be used to set up the pivot table.

 

 

3. Select fields from each table. For this example, set the Country field for Rows and the Amount for Values.

 

 

4. This summarizes the order amount for each country in the pivot table.

 

Tip: Try using some shortcuts when you’re working with pivot tables.

Link Tables in Google Sheets

Google Sheets doesn’t have anything quite like Excel Tables. To link two tables of data in Google Sheets, you need to use formulas – with the VLOOKUP Function and/or similar functions – and create a single table.