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 QUERY and IMPORTRANGE functions. Note: 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.

Before we move onto the matter of importing or linking data between different spreadsheets, let’s quickly cover how to import data between different sheets within the same spreadsheet. First, select the cell where you want the imported data to appear then type = followed by the name of the sheet you want to link to and the cell you want to link So in our case we’ll link the data in cell A1 from “Sheet2”: That data will now appear in your first sheet. If you prefer to pull a whole column, you can type your equivalent of the following:

The 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: 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 – ImportRange and 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. With your spreadsheet all set, how about pick up some more tricks? Here’s how to use iCloud keychain in Windows, and we can also indulge your nostalgia by showing you how to add the Windows 95 startup sound to Windows 10 and 11.