IMPORTRANGE in Google Sheets: pull data from another spreadsheet

IMPORTRANGE takes two pieces of text: which spreadsheet, and which range in it. For the spreadsheet, paste its full URL from the address bar. Both arguments go in double quotes.

You can also give just the key, the long ID between /d/ and /edit in the URL. It does the same thing and keeps the formula shorter. Or keep the URL in a cell and point at the cell, so several formulas share it.

Put the tab name in front of the range with an exclamation mark: "Orders!A1:F13". If the name has a space or other punctuation, wrap it in single quotes inside the double quotes: "'Q3 Orders'!A1:F6".

Leave the tab name out and IMPORTRANGE reads the first tab in the file. That works until someone reorders the tabs, so naming the tab is safer.

A range like A1:F13 stops at row 13, so new orders added below it never arrive. Leave the row number off the end, as in Orders!A1:F, and the import runs to the bottom of the tab. Whole columns such as Orders!A:C work the same way.

An open range also brings in the blank rows at the bottom of the tab, so it spills further than the data. When you feed it to QUERY, add where Col1 is not null to drop them.

The first time a spreadsheet imports from another one, the formula shows #REF!. Hover over the cell and you'll see "You need to connect these sheets" with an Allow access button. Click it and the data appears.

To connect them you need to be able to open the source file. You only connect each pair of files once: after that, any editor of your spreadsheet can import any range from the source, and anyone who can see your spreadsheet sees the imported values, even if they can't open the source. Share the destination file with that in mind.

In the practice sheets on this page you'll see the same #REF! and an Allow access button before the import fills in.

To stack imports on top of each other, put them in braces with a semicolon between them. They need the same number of columns. Leave the header row out of the second one so it appears only once.

A comma instead of a semicolon puts the imports side by side, which needs the same number of rows.

IMPORTRANGE and QUERY are often used together: import the raw data, then pick, filter and total it in one formula. Because the imported data is an array, not a range in this sheet, QUERY refers to its columns by position: Col1, Col2 and so on, with a capital C. Column letters give #VALUE! here.

The text you compare against still goes in single quotes, and you can splice in a cell, as in "where Col3 = '"&B3&"'".

SORT works on an import directly. FILTER needs the data and a condition that are the same height, so import the condition column as well.

Each IMPORTRANGE is its own formula reading the other file. When you need the same data several times, it is often simpler to import it once into a tab of your own and point the other formulas at that.

IMPORTRANGE can sit inside a lookup, so the table you search lives in a different spreadsheet. With VLOOKUP, import the whole table. With XLOOKUP, import the column to search and the column to return separately.

Large imports and many IMPORTRANGE formulas can make a spreadsheet slow to load, and the cells show Loading… while they fetch. Import only the columns and rows you need rather than whole tabs, and import a table once instead of in many small pieces.

IMPORTRANGE brings values, not formatting. Currency signs, colors and number formats stay behind, and dates can arrive as plain numbers until you format the column as a date.