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.
Includes new rows automatically, but may download unnecessary empty cells. Prefer a bounded range such as A1:C1000 for better performance.
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 the user who granted access was removed from the source. 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 persistent "Loading…" can involve request limits, network delays, or a temporary service problem. Google caps each request at 10 MB of received data. Import narrower ranges, summarize in the source, and avoid long chains of linked spreadsheets.
#REF! 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.
Import once into a helper tab, then reference that local range from other formulas. Repeating this expression in separate cells still creates separate 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 | Checks hourly while the document is open; source changes can trigger updates | 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"?
Check access and the source range, reduce imported data, and retry if the service is temporarily unavailable. Import once into a helper tab and reference it locally rather than repeating IMPORTRANGE.
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
Sources
Last updated 2026-09-30. Supported Excel examples are checked against sample data. Google Sheets imports and external workbook references need a check in the target spreadsheet. Sample results do not establish correctness for every input.