IMPORTRANGE, end to end
IMPORTRANGE is a Google Sheets function that pulls a range from another spreadsheet: =IMPORTRANGE("spreadsheet_url","Sheet1!A1:C100"). It needs a one-time permission grant before it returns anything. Excel has no IMPORTRANGE — the equivalents are Power Query, a workbook link, or a cross-workbook reference, none of which are a single formula.
Pull a range from another Google spreadsheet
Not available in Excel — see the Excel section belowGoogle Sheets
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/ABC123/edit","Sheet1!A1:C100")The first argument is the URL or the key from it; the second is the sheet name and range as one text string.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
The syntax, and the permission step everyone misses
IMPORTRANGE takes exactly two text arguments. The first is the source spreadsheet — either the full URL or just the key portion between /d/ and /edit. The second is the sheet name and range, quoted together as a single string.
The first time you reference a new source spreadsheet, the cell returns #REF! and nothing else. This is not an error in your formula. Click the cell and a blue "Allow access" button appears; the connection only works after you grant it, and it is granted once per source-destination pair, not once per formula.
The range is a string, so the sheet name goes inside the quotes: "Sheet1!A1:C100", not "Sheet1"!A1:C100. If the sheet name has a space in it, wrap it in single quotes inside the double quotes.
Using the key rather than the full URL
=IMPORTRANGE("1AbC_dEfGhIjKlMnOpQrStUvWxYz","Sheet1!A1:C100")Google Sheets only.
A sheet name containing a space
=IMPORTRANGE(A1,"'Q1 Sales'!A1:D50")Google Sheets only.
Single quotes around the sheet name, inside the double quotes.
A whole column, open-ended
=IMPORTRANGE(A1,"Sheet1!A:C")Google Sheets only.
Cheaper than a huge fixed range, and it picks up new rows automatically.
Combining IMPORTRANGE with other text
IMPORTRANGE returns an array, and arrays do not concatenate the way single values do. Joining "&" onto an imported range gives you only the first cell, which is the trap behind the "importrange concatenate" question.
To join imported columns row by row, wrap the operation in ARRAYFORMULA so it applies down the whole array. To collapse an imported range into one string, use TEXTJOIN.
Join two imported columns, row by row
=ARRAYFORMULA(IMPORTRANGE(A1,"Sheet1!A:A")&" — "&IMPORTRANGE(A1,"Sheet1!B:B"))Google Sheets only.
Without ARRAYFORMULA this returns a single joined value from the first row only.
Collapse an imported range into one string
=TEXTJOIN(", ",TRUE,IMPORTRANGE(A1,"Sheet1!A2:A100"))Google Sheets only.
Filter what you import instead of importing everything
=QUERY(IMPORTRANGE(A1,"Sheet1!A:D"),"select Col1, Col4 where Col3 = 'Paid'",1)Google Sheets only.
QUERY over IMPORTRANGE uses Col1, Col2… positional names — the source headers are not available.
Why IMPORTRANGE stops working
#REF! with an "Allow access" prompt means the permission has never been granted, or it was revoked when the source file changed owner. Grant it again from the cell.
#REF! with "resource at cell not found" usually means the sheet name in the string no longer matches — someone renamed the tab in the source. The range string is text, so a rename cannot update it the way a normal reference would.
"Internal error" or "Loading…" that never resolves is nearly always volume. IMPORTRANGE has practical limits on cells pulled and on how many separate imports one file can run. Import narrower ranges, or consolidate several imports into one call and slice the result locally.
#N/A with "Array result was not expanded" means something is sitting in the cells the imported array needs. Clear the block below and to the right of the formula.
Import once, slice locally, instead of many imports
=INDEX(IMPORTRANGE(A1,"Sheet1!A:D"),0,2)Google Sheets only.
One import feeding several INDEX calls is far cheaper than four IMPORTRANGE calls.
Show a readable message instead of a raw error
=IFERROR(IMPORTRANGE(A1,"Sheet1!A:C"),"Source unavailable — check access")Google Sheets only.
Use sparingly: it also hides the "Allow access" prompt you need to click.
The Excel equivalent — there is no function
Excel has no IMPORTRANGE and no single-formula way to pull a live range from a different workbook stored elsewhere. Searches for "importrange equivalent for excel" are looking for something that does not exist, and it is worth knowing that before spending an afternoon on it.
What Excel does have is Power Query (Data → Get Data), which is the closest genuine equivalent: it connects to another workbook, a folder, a database or a web source, transforms the data, and refreshes on demand or on open. Unlike IMPORTRANGE it is a query, not a formula, so it lands as a table rather than a spilled array.
For a live reference to another open workbook, an external link — ={'[Budget.xlsx]Sheet1'!A1:C100} — works, but only reliably while both files are open and in a stable location. It is fragile enough that Power Query is the better answer in almost every case.
If the source is a Google Sheet and the destination must be Excel, publish the sheet to the web as CSV and connect Power Query to that URL. That gives you a refreshable one-way link.
External workbook reference (both files open)
='[Budget.xlsx]Sheet1'!A1Breaks when the source moves or is renamed. Power Query is more durable.
Shown as a mistake to avoid — do not use this one.
Pulling data across files
| Need | Google Sheets | Excel |
|---|---|---|
| Live range from another file | IMPORTRANGE | Power Query, or an external link |
| Filter while importing | QUERY(IMPORTRANGE(...)) | Power Query filter step |
| Import a web table | IMPORTHTML | Power Query from Web |
| Import a CSV by URL | IMPORTDATA | Power Query from Web |
| Refresh behaviour | Automatic, ~30 min cache | On demand or on open |
Frequently asked
Why does IMPORTRANGE show #REF!?
Most often the one-time permission has not been granted. Click the cell and press "Allow access". If the prompt does not appear, the sheet name inside the range string probably no longer matches the source tab.
Is there an IMPORTRANGE equivalent in Excel?
No single function. Power Query (Data → Get Data) is the closest equivalent and is the recommended route; an external workbook reference works but is fragile once files move.
How do I concatenate IMPORTRANGE results?
Wrap the whole expression in ARRAYFORMULA so the join applies to every row, or use TEXTJOIN to collapse the imported range into one string. Joining with & alone returns only the first cell.
Why does IMPORTRANGE say "internal error"?
Usually volume. Reduce the size of the range, or replace several IMPORTRANGE calls with one call sliced locally by INDEX, which counts as a single import.
Can IMPORTRANGE pull from a specific worksheet?
Yes — the sheet name is part of the second argument, as one text string: "Sheet1!A1:C100". Wrap a name containing spaces in single quotes inside the double quotes.
Stop writing these by hand
Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.
Related
Last reviewed 2026-08-23. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.