In this tutorial, you will learn how to link google sheets together.
When you’re working in Google Sheets, you may find that sometimes the data you need is spread across multiple workbooks, making it hard to work with. While you could copy-paste data between files to ensure everything is in the same place, this process can be slow and must be redone every time data is updated in one of the data source spreadsheets, making this method time consuming and error-prone. The better solution is to create a direct link to the data you need so everything updates automatically when source data changes. Fortunately, it’s surprisingly easy to do this. Just follow these steps:
For this tutorial, we’ll assume a Primary spreadsheet that pulls data from a single range in a Source Data spreadsheet, but the steps below can be repeated as many times as needed to aggregate all your data.
Open the source data spreadsheet in your browser and copy the spreadsheet URL from the URL bar. Save this somewhere such as a word processor document or a spreadsheet cell. You’ll need it later
Take note of the data range you want to import into the primary spreadsheet. For example, Sheet1!A3:A in the screenshot below. Note that you must include the sheet name in this reference as you would if referencing a different sheet in the same spreadsheet
In the primary spreadsheet, choose a cell for the import range formula. Be sure the cell has enough blank cells after it to accommodate the full size of the range from Step 2, otherwise you will get errors. For this example, we’ll need a blank column as long as the column in the data source doc
Type the following formula into the cell, replacing “spreadsheetURL” with the URL from Step 1 and “Sheet1!A3:” with the range you want to import. Be sure both the spreadsheet URL and the range reference are wrapped in quotes: “=IMPORTRANGE(“spreadsheetURL”, “Sheet1!A3:A”)”
Hit enter and you will get an error if it’s the first time linking these spreadsheets. Hover over the error and a popup will appear with a blue Allow Access button. Click on it.
The linked data will populate. Any changes made in the data source spreadsheet will automatically populate in the primary spreadsheet
One drawback to using this method is that layout changes in the data source spreadsheets can cause problems. This is because the imported range reference is hardcoded and won’t update if the cells it references are moved. Using named ranges in place of the A1 notation range reference can fix this problem
In this tutorial, I covered how to link google sheets together. Want more? Check out all the Google Sheets Tutorials.