Most of us are familiar with the basics of using a spreadsheet in Google Sheets; we freeze certain rows and columns, we set up calculations, we organize our data into pies and graphs and charts to help visualize it. But why stop at that?
The next logical step in bolstering your spreadsheets is to link data between different spreadsheets, grabbing the exact data or ranges of data you want and sending them to a whole new place! Here we’ll show you how to do that using the
Note: note that when linking data to another spreadsheet, you’ll need to click the cell and “Allow Access” after entering the formula, otherwise the data won’t appear.
How to Link Data Using IMPORTRANGE
The first, most fundamental method of linking data between sheets is to use the
IMPORTRANGE function. Here’s the syntax for how IMPORTRANGE transfers data from one spreadsheet to another:
The “spreadsheet key” is the long mix of numbers and letters in the URL for a given spreadsheet.
The “range string” is the name of the exact sheet you’re pulling data from (called “Sheet1,” “Sheet2,” etc. by default), followed by a ‘!’ and the range of cells you want to pull data from.
Here is the sheet we’ll be pulling data from:
We’re going to be pulling data from this spreadsheet between cells A1 and D100. The formula to do that is entered into the receiving spreadsheet and looks like this:
This imports data from another spreadsheet, specifically from a sheet within that called “Sheet1,” where it pulls all the data between cells A1 and D100. Once you set this up, the data should appear just as it does in the source sheet.
Using QUERY to Import Data More Conditionally
IMPORTRANGE is fantastic for moving bulk data between sheets, but if you want to be more specific about what you want to import, then the
Query function is probably what you’re looking for. This will search the source sheet for certain words or conditions you set, then pull corresponding data from the same row or column.
So for our example we’ll again pull data from the below sheet, but this time we’re going to grab only the ‘Units Sold’ data from Germany.
To grab the data we want, we’ll need to type the following:
=QUERY( ImportRange( "1ByTut9xooZdPIBF55gzQ0Cdi04owDTtLVc_gPGtOKY0", "Sheet1!A1:O1000" ) , "select Col5 where Col2 = 'Germany'")
Here, the “ImportRange” data follows exactly the same syntax as before, but now we’re prefixing it with
QUERY(, and afterwards we’re telling it to select column 5 (the “Units Sold” column) where the data in column 2 says “Germany”. So there are two “arguments” within the query –
select ColX where ColY = 'Z'.
You can use these formulas for all manner of automated data-linking, so let your creativity (or spreadsheet management skills, at least) run wild!
The above guide allows you to create sheets filled with data dynamically as the source sheet gets updated. Setting yourself up in this way is a big time-saver in the long run and a godsend to those who want to amalgamate many spreadsheets’ worth of bespoke data into one great big super sheet.
Have you discovered any interesting tricks in Google Sheets, or do you have another way of inter-linking your spreadsheets? Do share in the comments.