Related Rows link rows in multiple worksheets so your spreadsheets can work like tables in a relational database.
When you link worksheets with Related Rows, your spreadsheets can work like tables in a relational database, where editing one changes data in another. You can create relationships with rows in any workbook you have access to, even workbooks in different folders.
Related Rows are a great way to connect data across multiple workbooks, maintain single sources of truth, and avoid repetitive and error-prone data re-entry.
How Related Rows Work
Related Rows work via three specialized data types:
- Related Row: Create links between related data across multiple worksheets
- Related Row Lookup: Lookup a value from a specific column in an existing Related Row
- Related Row Rollup: Perform calculations across Related Rows and return aggregate, or summarized, data
The worksheet where the Related Row is created is known as the target worksheet, and the worksheet to which it is related is known as the source worksheet. Related Rows are linked to values in primary columns.
How to Create Related Rows
- Identify the column, range, or cell that you want to link with another worksheet.
- Change your selection’s data type to Related Row and select the related worksheet
- Save your selection, and select a row from the source worksheet in a Related Row cell
Related Rows created at the column level can be established with 1-way or 2-way links. This determines the direction in which information is shared across your related worksheets.
- With a 1-way link, data will only be populated in the target worksheet and not the source worksheet.
- With a 2-way link, data will be populated in the target worksheet and a new column that reflects the relationship will be created in the source worksheet.
Click on a Related Row value to expand the row and display information from the source worksheet, as well as information about other rows in the target workbook that are related to that row.
Related Row Lookups
The Related Row Lookup data type looks for the value of a cell in an existing Related Row, similar to a lookup function like VLOOKUP. Related Row Lookups bring data from source worksheets into target worksheets and help ensure that you aren’t duplicating data in multiple places.
In the example above, Column D in the Employees worksheet is a Related Row Lookup that pulls in the value from Column B in Regions, and Column D in the Regions worksheet is a Related Row Lookup that pulls in the value from Column B in the Employees worksheet.
Related Row Rollups
The Related Row Rollup data type lets you perform calculations across linked rows in other worksheets and return aggregate information. Related Row Rollups make it easy to quickly calculate aggregated values across multiple worksheets.
In the example above, Column C in the Regions worksheet is a Related Row Rollup that calculates the sum of related values in Column D of the Stores worksheet.
Related Row Rollups can also perform other types of calculations like sums and averages and identifying maximum and minimum values.
Learn More: Creating Databases with Related Rows
Take your data organization to the next level and start turning your workbooks into databases with Related Rows. Take a look at these resources to learn more:
- “Introduction to Related Rows and Relationships Between Data” – Learn more about
- “1-way and 2-way Links” – Explore the difference between the types of links established with Related Rows
- “Types of Relationships and Junction Tables” – Learn more about the concepts behind Related Rows and the different types of relationships you can create with them
Next, learn how to gather data from multiple worksheets and to analyze data and compile a single source of truth with Reports.