Quick Links
Do you need to import a range of cells from another Google Sheets spreadsheet? If so, the IMPORTRANGE function is designed to do just that! What’s more, if the source changes, so does the imported range. Here’s how to use this useful tool.
The IMPORTRANGE function inGoogle Sheetshas two arguments:
Argumentais the link (URL) to the Google Sheets file where the range you want to import is located—enclosed in double quotes—or a reference to a cell containing the URL.
Argumentbis the reference to the range. This can be cell references, a sheet name followed by cell references, a named range, a table name, or a column within a table—all of which must be in double quotes. Argumentbcan also reference a cell containing the cell references of the source data, and thisdoesn’tneed quotation marks.
Confused? Let’s explore each of these more closely.
Importing Data Using Direct Cell References
You can import data from one Google Sheets file to another by specifying the source URL and the relevant cell references. This is useful if you’re sure that the source data won’t change location or grow in size.
Let’s imagine you have two Google Sheets files open on separate browser tabs—File 1 contains a list of names in cells A2 to A11 that you want to import into File 2.
To do this, select the cell in File 2 where you want the list to be imported into, type the following formula—remembering to enclose both the URL and cell references in quotation marks—and press Enter:
The example formulas in this guide only show part of the URL for demonstration purposes. In your case, ensure you insert thewholeURL into the formula.
The first time you connect the active and referenced worksheets, you may see a #REF! error. If you do, hover over the cell containing the error to see one of two messages.
On the one hand, if you own both spreadsheets, Google Sheets will tell you that you need to grant permission to link the files. To do this, click “Allow Access.”
On the other hand, if you don’t own the source spreadsheet and haven’t been given edit access, you’ll be told that you don’t have permission to use the linked sheet in your IMPORTRANGE formula. In this case, paste the URL into your browser’s URL bar, click “Request Edit Access,” and wait for the owner to grant this request.
Once the correct permissions are activated, the range is imported successfully.
What’s more, any changes to the content of cells A2 to A11 in File 1 will be reflected in the imported data in File 2 after it refreshes automatically.
If the source data expands or changes location, the cell reference you used in argumentbwon’t adjust to this change, so you’ll need to adjust the formula accordingly.
By default, the IMPORTRANGE function imports from the first worksheet in the specified file, even if you use the URL of a different worksheet in the file. However, if you want to import a range from another worksheet, for argumentb, type the sheet name, followed by an exclamation mark, and then the cell references.
For example, this formula would import the data from cells A2 to A11 in Sheet 2 of the source file:
Pro Tip: Importing Data From Within the Same Google Sheets File
The IMPORTRANGE function is intended to import ranges from different Google Sheets files. To import data from one spreadsheet in a Google Sheets file to another spreadsheet in the same Google Sheets file, use the ARRAYFORMULA function. For example, typing:
dynamically copies the data from cells A2 to A11 in the same worksheet.
Similarly, typing:
into a cell in Sheet 2 duplicates the data in cells A2 to A11 in Sheet 1.
Importing Data Using Indirect References
In the examples above, argumentacontains the full URL of the source data, leading to lengthy formulas that are challenging to parse. Instead, to make the formula tidier, you could reference a cell that contains the source URL.
Here, cells C2 to C11 in File 1 contain a list of animals you want to import into File 2.
If you have edit access to File 1, copy its URL, and select a blank cell in File 2. Then, click the formula bar at the top of the spreadsheet so that the cursor is flashing, and press Ctrl+V (or right-click and select “Paste”), before pressing Enter.
If you paste the link directly into the cell, Google Sheets reformats the link, causing it to be incompatible with the IMPORTRANGE function. This is why it’s best practice to paste the URL into the formula bar after selecting the destination cell.
Next, select the cell where you just pasted the link, and click “Chip.”
This shortens the link into a file label, making your spreadsheet tidier and more professional.
Then, in another cell in File 2, type:
whereA1is the cell in File 2 containing the URL of File 1 that you just pasted, andC2:C11is the range in File 1 that you want to import.
The cell containing the URL doesn’t need to be in quotation marks.
Then, press Enter.
Rather than hard-coding the cell references in argumentb, you can type these into a separate cell in File 2, and reference that instead. As well as further shortening your formula, this method means that you can easily change the reference without having to edit the formula itself.
In this example, the formula:
in cell B2 takes the URL from cell A1, and the cell references from cell B1.
If you later realize that you want the imported range to be static (rather than allowing it to update according to the source data), simply select and copy the range, and press Ctrl+Shift+V topaste the data as values.
Importing Data From Named Tables and Ranges
The IMPORTRANGE function in Google Sheets can also import tables, columns in tables, and ranges.
In this example, File 1 contains a table named T_Fruits, and you want to import the column named Fruits into File 2.
So, after ensuring you have edit access to File 1, in a blank cell in File 2, type:
where the URL is the link to File 1,T_Fruitsis the name of the table, andFruitsis the column within that table that you want to import.
If you type only the name of the table (and not a column header), the whole table will be imported.
The benefit of this approach is that if more rows are added to the data, the IMPORTRANGE function will pick these up, meaning the changes will be reflected in the imported version. That said, if you rename the table or column headers, you’ll need to update argumentbof the IMPORTRANGE formula accordingly.
In a similar fashion, argumentbcan also be a named range.
For example, cells H2 to H11 in File 1 are named R_Cities.
As a result, the formula in File 2 can import this data by referencing the named range:
Nesting IMPORTRANGE in Other Functions
IMPORTRANGE doesn’t have to be used on its own in Google Sheets—it can also be used alongside other formulas to perform calculations on data in other Google Sheets files.
In this example, let’s imagine File 1 has a list of scores in cells J2 to J6. Your aim is to use the IMPORTRANGE function with theSUM functionto evaluate these cells, sum their values, and return the result in File 2.
To do this, in a blank cell in File 2, type:
where the IMPORTRANGE function references cells J2 to J6 in File 1, and the SUM function adds the values they contain.
According to Google, using this approach is faster than first using IMPORTRANGE to import the data, and then using a separate SUM function to sum the imported range.
The IMPORTRANGE function can’t reference a cell that contains another volatile function—likeNOWor therandom number functionsRAND and RANDBETWEEN—as doing so would potentially overload the spreadsheet’s resources. The only exception is the TODAY function, since this only updates once daily.
Points to Note When Using the IMPORTRANGE Function
Before you go ahead and use IMPORTRANGE in your Google Sheets spreadsheets, here are some final pointers to bear in mind:
Because it’s an online software, manyprefer Google Sheets to its desktop competitors, such as Microsoft Excel. Indeed, to perform the same importing actions in Excel, you would need to use the Power Query Editor tool, a route that requires many more steps.
