IMPORTRANGE in Google Sheets: the complete guide

IMPORTRANGE pulls a range of cells from one spreadsheet into another. It is the closest thing Google Sheets has to a built-in answer for "show this person part of my data," and it's free. Worth learning properly, including the four or five ways it will frustrate you.

The syntax

=IMPORTRANGE(spreadsheet_url, range_string)

Both arguments are text, so both need quotes:

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

• The URL can be the file ID alone. The long string between /d/ and /edit works just as well and keeps the formula readable.
• The range takes a tab name. Omit it and you get the first tab, which silently becomes the wrong tab the day someone reorders them, so always name it.
• Open-ended ranges work. "Orders!A:F" keeps up as rows are added, where "Orders!A1:F500" quietly stops at row 500.
• Tab names with spaces are fine inside the quotes: "Q4 Budget!A:D". Names containing an apostrophe are the ones that need care.

The first run always looks broken

You'll type a correct formula and get #REF!. This is expected. Click the cell, and a prompt appears offering Allow access. Click it and the data loads.

That click does something worth understanding. It creates a standing authorization between the two files, made by you, using your access to the source. From then on, anyone who can open the destination sees the imported values, whether or not they could open the source themselves. That's precisely what makes IMPORTRANGE useful for sharing, and precisely what makes it a liability if you later widen who can see the destination file.

The errors you will hit

Hover the cell to read the actual message. The same error code covers several different causes.

How often it updates

Not instantly. Google refreshes imported ranges periodically rather than on every keystroke, so a change in the source shows up in the destination minutes later, not seconds. Nothing is wrong when this happens.

That matters in practice. If someone is watching a number while you edit it, IMPORTRANGE will feel broken to both of you. Say plainly that the view lags, or use something live.

What doesn't come across

IMPORTRANGE moves values. Everything that makes a spreadsheet legible stays behind:

• Colours, fonts, borders, and column widths
• Conditional formatting
• Data validation, dropdowns, and checkboxes
• Merged cells and frozen rows
• Notes, comments, and images

You can rebuild formatting in the destination. Conditional rules applied there work fine on imported values, but you're maintaining it in two places from then on.

Where it gets genuinely powerful

IMPORTRANGE returns a range, so anything that accepts a range accepts it. Most people never get past the basic form, which is a shame.

Send a filtered subset rather than the whole tab:

=QUERY(IMPORTRANGE("1AbC...", "Orders!A:F"),
       "select Col1, Col2, Col5 where Col4 = 'Acme' order by Col2 desc", 1)

Or filter without QUERY's syntax:

=FILTER(IMPORTRANGE("1AbC...", "Orders!A:F"),
        IMPORTRANGE("1AbC...", "Orders!D:D") = "Acme")

Or stack several sources into one list:

={IMPORTRANGE("id_north", "Sales!A2:D");
  IMPORTRANGE("id_south", "Sales!A2:D")}

This is IMPORTRANGE's real advantage over any copy-based approach: you control exactly which rows and columns leave the source, and you can reshape them on the way out.

Keeping it fast

• One wide import beats twenty narrow ones. Each formula is its own fetch. Pull the block once into a staging tab, then reference that tab locally.
• Don't repeat the same import inside a formula. The FILTER example above calls IMPORTRANGE twice, which is worth avoiding. Import once to a helper range and filter that.
• Bound your ranges sensibly. A:F is good; A:Z on a sheet using six columns is twenty wasted columns on every refresh.
• Chains multiply lag. A imports from B which imports from C: each hop adds its own refresh delay.

When to stop using it

IMPORTRANGE has one hard limit, and no amount of cleverness gets around it: it only reads. The cells it produces are formula output. Nobody can type into them, and nothing typed anywhere else finds its way back to your source.

So the moment your collaborator needs to change something (confirm a quantity, update a status, fill in their half of a table), you've reached the end of what a formula can do. You will know when it happens: people start emailing you their updates for you to retype. At that point IMPORTRANGE was the wrong tool, and what you needed was a shared tab with two-way sync.

Frequently asked questions