Google Sheets IMPORTRANGE: Setup, Uses, and Common Fixes

Google Sheets’ IMPORTRANGE function brings a cell range from one spreadsheet into another and keeps the imported results connected to the source. Enter =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10") in the destination sheet, then select Allow access when prompted.

What IMPORTRANGE does

IMPORTRANGE is a Google Sheets function for linking data across separate spreadsheet files. The formula lives in the destination spreadsheet, while its arguments identify the source file and the cells to retrieve. It is useful when you want a summary, report, or working copy to draw on data maintained in another file.

The function returns the contents of the selected range, including the results of formulas in the source cells. It does not copy the source sheet’s formatting, charts, or other layout features. Think of it as a connection to cell data rather than a full copy of a worksheet.

The connection is not necessarily instant: changes in the source can take time to appear in the destination. It is also not a two-way sync. Editing an imported result in the destination does not update the source; the imported cells are controlled by the formula.

Syntax and a basic example

The function takes two required arguments: the source spreadsheet’s URL (or spreadsheet key) and a text string specifying the tab and range. Put the formula in the upper-left cell where the imported block should appear, and leave enough empty space for the results to fill.

For a source file with the tab Inventory, with product names in A2:A50 and quantities in B2:B50, enter the following in the destination cell where the two columns should begin:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123example/edit", "Inventory!A2:B50")

The example URL is illustrative; use the actual source file’s URL. You can also use its spreadsheet key, the long identifier between /d/ and the next slash in the URL. The range string uses A1 notation: a tab name, an exclamation mark, and the cell address. For an entire set of columns, a range such as Inventory!A:B is possible, though a bounded range is usually more efficient.

If a tab name contains spaces, put single quotes around the tab name inside the range string. With a tab called Quarterly Sales and source data in A1:D20, the formula to place in the destination’s starting cell is:

=IMPORTRANGE("spreadsheet_url", "'Quarterly Sales'!A1:D20")

The single quotes are part of the range notation; the double quotes mark the text argument. Tab names must match the source, including spaces and punctuation.

How to set up an import

Open the destination spreadsheet in Google Sheets in a web browser, and select the cell where the imported range should start.
Enter an IMPORTRANGE formula with the source URL and the exact tab-and-range string. Replace the sample URL and range with your own.
Press Enter. The first attempt commonly displays #REF! with a prompt to connect the spreadsheets.
Select Allow access. Once the connection is authorized, the requested cells should populate in the destination.

If no access button appears, check the error text and whether the signed-in Google account can open the source file. The person setting up the connection must have permission to view the source. If the owner has not shared it with that account, request access to the source first. A formula cannot bypass the source file’s access controls.

Permissions and information safety

Authorization matters beyond the person who first enters the formula. When access is granted between a destination spreadsheet and a source, editors of the destination can use IMPORTRANGE to request other ranges from that source. In practice, someone who can edit the destination may be able to change a formula to retrieve additional source data that the connection can reach.

That is a reason to treat a shared destination as part of the access boundary. Before giving others edit permission, consider what is in the source file and whether destination editors should be able to retrieve it. For sensitive information, a safer approach may be to create a separate source file containing only approved data, then connect to that file instead.

Share the source file only with the accounts that should be able to view its data.
Limit editor access to the destination when its connection reaches confidential information.
Use a sanitized source spreadsheet if people need only a subset of a larger private file.

Removing someone’s access to the source or changing sharing settings can affect their ability to use the connection. Review the source and destination permissions together; protecting cells in the destination is not a substitute for controlling access to the source.

Choosing a range and keeping imports manageable

Import the smallest useful range rather than whole columns or an entire tab by default. A bounded range such as A2:F500 can avoid bringing in thousands of blank cells and makes the formula’s purpose easier to understand. If the dataset grows, adjust the endpoint or choose a larger bounded range with reasonable headroom.

Use a clear destination layout: leave the cells below and to the right of the formula output empty, because the imported results need room to expand. If another value or formula blocks that space, Sheets may report that the array result could not expand. Put notes or calculations beside the imported block, not inside its expected output area.

For a report that needs just a few fields, it may be better to organize the source tab so those fields sit together, then import one rectangular range. This keeps the connection simpler than building a report from numerous separate imports. A source tab dedicated to reporting can also make it clearer which data is intended to be shared.

Updates, limits, and performance

IMPORTRANGE is designed to refresh linked data, but do not treat it as a real-time database feed. Updates may be delayed, and calculations that depend on imported data can add further waiting. This is usually acceptable for reference lists and periodic reports, but a workflow that depends on immediate updates may need a different design.

Google Sheets documents a maximum of 10 MB of data received per IMPORTRANGE request. Large imports can therefore fail or make a spreadsheet cumbersome. Narrow the requested cells, remove unnecessary columns in the source, or split a large project into deliberately sized datasets rather than importing everything indiscriminately.

Long chains of linked spreadsheets can also slow recalculation and make problems harder to trace. For example, if file B imports from file A and file C imports from B, a delay or error in A can affect C as well. When practical, have destination reports connect directly to a stable source, and avoid circular dependencies where files ultimately rely on one another.

Common errors and how to fix them

#REF! asking to connect: select Allow access. If the prompt is absent, verify that the correct Google account can open the source.
#REF! with a range or reference message: check the spreadsheet URL, tab spelling, punctuation, and cell range. A tab with spaces needs single quotes inside the range string.
An output expansion error: clear cells that overlap the range the formula needs to populate. The formula must have open space for its full result.
Data appears stale: allow time for the connection to update, then confirm that the source itself has the expected latest values. Check whether source formulas have finished calculating.
A large request fails: reduce the imported range. Broad whole-column requests may include far more cells than the report needs.

When troubleshooting, test a small known range first, such as a few cells on the intended tab. If that succeeds, expand the range gradually. This helps distinguish an access issue from a typo, blocked output area, or overly large request.

IMPORTRANGE compared with alternatives

Use IMPORTRANGE when the source is another Google Sheets file and you want a formula-based link to its cell data. If the information is already in another tab of the same spreadsheet, a normal cell reference is usually simpler; there is no need to connect a file to itself through an import function.

If you need a one-time snapshot rather than a continuing link, copying and pasting the data may be more appropriate, though later source edits will not flow through. If you need to combine, clean, or regularly load data from external systems, a dedicated connector or another data-import workflow may fit better. For Excel, IMPORTRANGE is not a supported function; Excel uses different approaches, including external workbook links and Power Query, depending on the task.

Frequently Asked Questions

Does IMPORTRANGE update automatically?

It is intended to refresh data from its source, but updates are not guaranteed to appear instantly. Allow time for recalculation, especially when the source or destination contains other formulas.

Can I use IMPORTRANGE between tabs in the same spreadsheet?

It is intended to import from a separate spreadsheet file. For another tab in the same file, use an ordinary sheet reference such as =Inventory!A1:B10 instead.

Why does IMPORTRANGE show #REF!?

The usual first-time cause is that the two files have not been connected; select Allow access. Other causes include missing permission to the source, an incorrect tab or range, or an output area that is blocked.

Does IMPORTRANGE copy formatting?

No. It returns cell data and formula results, not the source’s formatting, charts, or worksheet design. Format the destination separately if you want the imported values to have a particular appearance.

Can people who edit my destination see the source data?

They may be able to use the authorized connection to request other ranges from the source. Share the destination carefully, and use a source file containing only data that its destination editors are allowed to access.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.