See all How-To Articles

How to Link Tables in Excel & Google Sheets

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

 

link-tables-intro

 

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.

 

link tables ribbon

 

  1. In the Manage Relationships box, click New…

link tables relationships new

 

  1. 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.

 

link tables table select

 

  1. 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.

 

link tables select field

 

  1. 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.
  2. Click OK to create the relationship.

 

link tables create relationship

 

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

link tables manage relationship

  1. 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.

 

link tables pivot from data model

 

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

 

link tables pivot select tables

 

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

 

link tables pivot select fields

 

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

 

link tables pivot summarize

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.

 

link tables gs link tables

See all How-To Articles