IMPORTRANGE pulls a range of cells from a different Google Sheets file into the one you're working in, and keeps it in sync when the source changes. It's the standard way to build a master report on top of separate files for each team, client or month.
Syntax
=IMPORTRANGE(spreadsheet_url, range_string)
spreadsheet_url
The other file's full URL from the browser's address bar, or just its key (the long ID between /d/ and /edit). It must be text in quotes or a reference to a cell that holds it.
range_string
The tab name and range as text, e.g. "Orders!A1:D5". Without a tab name, the first tab is used. Tab names with spaces need single quotes: "'Q1 Orders'!A1:D5".
Practice IMPORTRANGE
Task 1 of 4
The URL of the Regional Orders spreadsheet is in A2. Import its whole Orders tab (A1:D9) into this sheet, starting in C1.
C1
fx
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
IMPORTRANGE examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
=QUERY(IMPORTRANGE(A2, "Orders!A1:D5"), "select Col2, sum(Col3) group by Col2", 1)
Client
sum Amount
Brightline
$1,200.00
Northwind
$750.00
Orbit Foods
$875.00
Import and summarise in one formula. Imported columns are Col1, Col2…, not letters.
=IMPORTRANGE(A2, "Sheet1!A1:D5")
#REF!
The source has no tab called Sheet1.
Fixing the #REF! "You need to connect these sheets" error
The first time a spreadsheet imports from another file, IMPORTRANGE shows #REF!. Click the cell, hover over it and press Allow access. This is a one-time permission per pair of files, and you must have at least view access to the source file yourself. Once it's granted, everyone who can edit the destination file sees the data.
Other reasons IMPORTRANGE isn't working
"Spreadsheet not found": the URL or key is wrong, or the file was deleted. Copy the URL again from the address bar.
Wrong tab name: the part before ! must match the tab's name exactly, including spaces. Use single quotes around names with spaces: "'Q1 Orders'!A1:D50".
The range isn't in quotes:IMPORTRANGE(A2, Orders!A1:D5) without quotes is a formula error. The range is text.
#REF! after it used to work: something is blocking the spill. Clear the cells below and to the right of the formula.
Loading for a long time: very large imports, or many IMPORTRANGE formulas pointing at the same file, slow everything down. Import one block once and reference it locally, instead of importing the same data in many cells.
Filtering and summarising imported data
Wrap IMPORTRANGE in QUERY to bring in only what you need: =QUERY(IMPORTRANGE(A2, "Orders!A1:D500"), "select Col1, Col3 where Col3 > 100", 1). Because the data comes from a function, columns are named Col1, Col2… in the order of the imported range, not by their letters in the source file. FILTER, SORT and SUM work on imported data too.
Practising IMPORTRANGE here
A browser practice page can't open your Google Drive, so the exercises on this page use a simulated source file, shown under the practice grid. The formulas and error messages match what you'd see in Sheets.
Tips and common mistakes
Keep the source URL in a cell and point every IMPORTRANGE at it. When the source file changes, you update one cell.
Import open-ended ranges like "Orders!A1:D" so new rows in the source come through without editing the formula.
IMPORTRANGE brings in values only, not formatting, notes or formulas. Format the destination columns yourself.
Imported data is read-only in the destination file. To change it, edit the source.