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.
- In the Ribbon, go to Data > Data Tools > Relationships.
- In the Manage Relationships box, click New…
- In the Create Relationship box, from the Table drop down on the left, choose the table that has multiple entries of the common field (i.e., Customer ID), and then choose the Related Table, which must have exactly one entry for each value in the common field.
- In the Column (Foreign) drop down on the right, choose the common field name (e.g., Customer ID). Here, in Table 1, the Customer ID can appear multiple times as each customer can place multiple orders.
- In the Related Column (Primary) drop down, choose the common field once again. This time, the common field is Primary, meaning the value in the field (Customer ID) can only appear once. There are no duplicate customer details in Table2 – a unique Customer ID is stored in each row.
- Click OK to create the relationship.
- Confirm that the new relationship you defined shows up in the Manage Relationships box.
- Click Close.
Create Pivot Table From Multiple Tables
To create a pivot table out of multiple tables, use a data model.
- In the Ribbon, go to Insert > Pivot Table > From Data Model.
- Choose New Worksheet and click OK.
- Both tables show up in the PivotTable Fields list, and fields from both tables can be used to set up the pivot table.
- Check fields from each table. For this example, set the Country field for Rows and the Amount for Values.
- 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.