How to Use IMPORTRANGE in Google Sheets: Comprehensive Guide

IMPORTRANGE pulls a range of cells from another Google Sheets file into the current one, and keeps it live. It is how you build dashboards that read from several spreadsheets, share a controlled slice of a private file, or split a large workbook into smaller connected ones.

Syntax

=IMPORTRANGE(spreadsheet_url, range_string)

spreadsheet_url: The URL of the source spreadsheet, or just its ID (the long string between /d/ and /edit in the URL). It must be in quotes unless it comes from a cell.
range_string: The range to import, in quotes, optionally with the sheet name: "Sheet1!A1:D20". Without a sheet name, the first sheet is used.

Example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AbC...xyz/edit", "Sales!A1:D100")

First use: allow access

The first time you use IMPORTRANGE between two files, the cell shows #REF! with a "Allow access" button. Click it once and the data appears. This is a per-pair permission: it is granted again for each new source file. You also need at least view access to the source file.

Examples

Import a whole sheet or open-ended range

=IMPORTRANGE("spreadsheet_id", "Orders!A:F")

Whole-column ranges grow with the source data. If you also want a tidy result, add A2:F to skip the header row.

Use cells for the URL and range

Keep the URL and range in cells so they are easy to change:

=IMPORTRANGE(A1, B1 & "!A:F")

Filter the imported data

IMPORTRANGE returns an array, so you can wrap it in other functions. Select only rows where the second column is Sales:

=QUERY(IMPORTRANGE("spreadsheet_id", "Orders!A:F"), "select Col1, Col3 where Col2 = 'Sales'", 1)

Imported ranges are referred to as Col1, Col2, Col3, and so on in QUERY. See QUERY.

Look up in another file

=VLOOKUP(A2, IMPORTRANGE("spreadsheet_id", "Prices!A:C"), 3, FALSE)

Combine several files

Stack ranges from different sources vertically with curly braces and a semicolon:

={IMPORTRANGE("id_1", "Data!A2:D"); IMPORTRANGE("id_2", "Data!A2:D")}

The ranges must have the same number of columns.

Tips for reliable imports

Use the ID rather than the full URL. It is shorter and works identically.
Import once, then reference locally. If you use the same source in many formulas, import it into a hidden "raw data" sheet once, and have other formulas read from that sheet. This is faster and counts as one connection.
Avoid excessive whole-file imports. Heavy chains of IMPORTRANGE inside IMPORTRANGE slow things down and can fail.
The result is read-only. You can't type over imported cells; edit the source file instead.
Changes can take a moment. Updates in the source usually appear within seconds to minutes, not instantly.

Common errors

#REF!: "You need to connect these sheets." Click Allow access. If no button appears, you might not have view access to the source file.
#REF!: "Array result was not expanded because it would overwrite data." The cells where the result spills are not empty. Clear them.
#REF!: "Imported content is empty." The range or sheet exists but has no data.
#REF!: "Unable to parse range". The sheet name or range is mistyped. Check spelling and capitalization, and use the form "Sheet name!A1:D20".
#ERROR! / "Internal error" or "Loading..." for a long time. The source is very large, or you have too many IMPORTRANGE calls. Reduce the range, or import fewer, larger blocks.
Result limit. Importing very large ranges can hit Google's size limits. Import only the columns you need.

Frequently asked questions

Is the data live? Yes. It refreshes when the source changes, with a short delay.

Can I import from an Excel file or CSV? IMPORTRANGE works only with native Google Sheets files. For CSV files online use IMPORTDATA; for Excel files, convert them to Google Sheets first.

Does it work if the source is private? Yes, if you have access to it. The people who view your file do not automatically get access to the source, but they see the imported values. Be careful when sharing a file with imported private data.

Why is it slow? Each IMPORTRANGE is a separate fetch. Fewer, wider imports are faster than many small ones.

Related Functions

QUERY: Filter and aggregate imported data in one formula.
FILTER: Return only the imported rows that meet a condition.
VLOOKUP: Look up values in an imported range.
ARRAYFORMULA: Apply a formula across a whole range.
IMPORTDATA: Import a CSV or TSV from a URL.

IMPORTRANGE