Use Related Rows to Link Spreadsheets

Related rows have long been an elusive concept in spreadsheets. If you have tables that should be connected, your options have been limited to functions such as VLOOKUP or INDEX/MATCH. These functions can pull data in from other sources, but they don’t develop a built-in two-way link.

Video tutorial about related rows
Video Tutorial

However, related rows are built into from the ground up. In other words, they work with no plug-ins or formulas. These rows create relationships between two tables with only a few clicks.

You can follow along with this tutorial by making your own copy of the 🔗template featured in this article.


To make this process easier, you can define header rows in each worksheet. After designating the header rows, you can set a data type for the column from the header row.

Spreadsheet table with a defined header row and a menu where you start to define the related row data type
Header Row Making it Easier to Define Data Type

Creating the Relation

Once you have decided which two columns will relate to each other, select the first column, then choose Edit data type. From the data types listed, choose Related row.

When you choose this data type, this indicates that you want to associate the values with data in another worksheet. The worksheet can be in the same file or a different file.

After you choose the worksheet, will pull in the values from that worksheet’s Primary column.

Register with Values from Vendors Worksheet
Primary column that the related field is pulling from
Source of the Labels Going to Register Worksheet

After creating the rows, you’ll notice that the destination table automatically has a column added that links back to the values in the source’s Primary column. This column is showing the unique value of every transaction for each vendor.

Related values showing up automatically in the second worksheet.
Automatically added to the second worksheet

Now you’re done creating your first set of related rows. After you do it the first time, you will probably find yourself using them time instead of VLOOKUP.

Leave a comment

© 2020 – 2023