IMPORTRANGE is the function that connects separate Google Sheets files. Its core usage is pulling a live range of data from one spreadsheet into a destination spreadsheet, with the output updating automatically when the source changes. This guide covers the syntax, the step-by-step setup, how to combine imports from multiple sheets, known limitations, and every common error with its fix.

If you are looking for functions that import data from external sources rather than other Google Sheets files, Google Sheets also has IMPORTDATA (for CSV or TSV files at a public URL), IMPORTHTML (for tables on web pages), and IMPORTXML (for structured XML or HTML data). IMPORTRANGE is specifically for pulling data between Google Sheets files.

Table of Contents

IMPORTRANGE Syntax and Parameters

The function takes two required arguments, both enclosed in double quotes:

=IMPORTRANGE("spreadsheet_url", "range_string")
Parameter What It Does Example
spreadsheet_url The URL of the spreadsheet you want to import from. You can use the full URL or just the spreadsheet ID (the alphanumeric string between the slashes in the URL). "https://docs.google.com/spreadsheets/d/abc123..."
range_string The sheet name and cell range to import, in the format "SheetName!A1:C20". "Sheet1!A1:C20"

Range String Format Options

The range string tells Google Sheets which sheet name and which cells to pull from the source file. Three formats are supported:

  • Specific sheet (recommended): "Employees!A1:B10"
  • Default sheet: "A1:B10" โ€” pulls from the first tab in the file, which can cause problems if tab order changes
  • Single cell: "Summary!B5"

Always include the quotation marks around both arguments and the exclamation point between the sheet name and the cell range. Missing either one is the most common cause of a formula parse error.

How to Use IMPORTRANGE in Google Sheets (Step by Step)

This example pulls stock levels from a separate Warehouse spreadsheet into a master Inventory Template.

  1. Get the URL of the spreadsheet. Open the Warehouse file and copy the full URL from the browser bar. This is the URL of the file you want to import from. You can also use just the spreadsheet ID, which is the long string of characters between /d/ and /edit in the URL.
  2. Select the destination cell. Open your destination spreadsheet and click the cell where you want the imported data to begin. This can be an existing sheet or a new spreadsheet created specifically to consolidate data.
  3. Enter the formula. Type =IMPORTRANGE(, paste the URL in double quotes, add a comma, then type the range string in double quotes, and close the parenthesis. For example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123/edit", "Warehouse!A2:C50")
  1. Press Enter. The cell will show a #REF! error message. This is expected. It is not a syntax problem โ€” it means Google Sheets is waiting for permission to connect the two files.
  2. Grant access. Hover over the cell with the error and click the blue Allow Access button that appears. You only need to do this once per source spreadsheet. Once access is granted, all IMPORTRANGE formulas pointing to that source will work without re-authorizing.

After access is granted, the data will populate automatically and stay in sync with the source file.

How to Import from Multiple Sheets at Once

You can stack data from multiple source spreadsheets into a single destination spreadsheet using array syntax with curly braces. This is one of the most practical uses of IMPORTRANGE for anyone building a consolidated report or a master Dashboard. A common setup is to create a new spreadsheet specifically as the consolidation destination, then point multiple IMPORTRANGE formulas at it from different source files.

To combine two sources vertically (rows stacked on top of each other), use a semicolon inside the curly braces:

={IMPORTRANGE("URL1", "Sheet1!A2:C50"); IMPORTRANGE("URL2", "Sheet1!A2:C50")}

To combine two sources horizontally (columns placed side by side), use a comma:

={IMPORTRANGE("URL1", "Sheet1!A2:A50"), IMPORTRANGE("URL2", "Sheet1!A2:A50")}

Two things to keep in mind with this approach. First, for vertical stacking to work, both ranges must have the same number of columns. Second, each source spreadsheet still requires its own access grant. If either source has not been authorized, the combined formula will return an error. The easiest fix is to enter each IMPORTRANGE formula separately in a temporary cell first, grant access to each, then combine them into the array formula.

Performing Calculations on Imported Data

You can wrap IMPORTRANGE inside other functions to calculate as data arrives, rather than importing raw data and calculating separately. This is useful for building dynamic reports such as a Profit and Loss statement.

SUM an Imported Range

=SUM(IMPORTRANGE("URL", "Sales!B2:B100"))

Filter Rows with QUERY

QUERY combined with IMPORTRANGE lets you pull only the rows that meet a condition, rather than importing an entire dataset and filtering afterward:

=QUERY(IMPORTRANGE("URL", "Data!A:E"), "SELECT * WHERE Col2 > 500")

One important difference when using QUERY on imported data: you must use Col1, Col2, Col3 notation to refer to columns, not the standard A, B, C column letters. The imported range is treated as a standalone virtual table, so column letters from the source sheet do not apply.

Use Named Ranges to Keep Formulas Readable

If your IMPORTRANGE formulas are getting long or you reference the same source range in multiple places, consider using a named range in the source spreadsheet. In the source file, go to Data > Named ranges, assign a name to the range (for example, WeeklySales), then reference that name in IMPORTRANGE:

=IMPORTRANGE("URL", "WeeklySales")

If the data range changes size or moves in the source file, you only need to update the named range definition in one place rather than editing every formula that references it.

Using IMPORTRANGE with VLOOKUP

A common pattern is combining IMPORTRANGE with VLOOKUP to look up a value from a separate spreadsheet without manually copying data over. The IMPORTRANGE function serves as the table array argument in VLOOKUP:

=VLOOKUP(A2, IMPORTRANGE("URL", "Employees!A2:C100"), 3, FALSE)

This formula looks up the value in A2 against column A of the imported range, and returns the value from the third column. The first time you use this, you may need to enter the IMPORTRANGE portion alone in a separate cell first to trigger the access grant, then build the full VLOOKUP formula once access is authorized.

For a full walkthrough of this pattern, see our guide on VLOOKUP from Another Sheet in Google Sheets.

Known Limitations of IMPORTRANGE

IMPORTRANGE is useful but has real constraints worth knowing before you build anything around it.

Formatting does not transfer. Only data values come across. Cell colors, conditional formatting, checkboxes, text styles, number formatting, and column widths stay in the source file. You will need to reapply any formatting in the destination spreadsheet manually. This is a meaningful difference from copying data in Excel, where paste options can preserve formatting and column widths alongside values.

50-source limit per file. A single Google Sheets file can reference a maximum of 50 different source spreadsheets via IMPORTRANGE. If you are consolidating data from many files, you may hit this ceiling.

10MB transfer limit. Very large datasets can fail or slow down significantly. If you are importing a large table, consider filtering with QUERY to reduce the size of the import rather than pulling the entire dataset.

Source URL dependency. The formula is linked to the source file’s URL. If the source file is moved, deleted, or its sharing permissions change, the import breaks. There is no built-in way to detect this automatically.

Update delay. IMPORTRANGE is live but not instant. Changes in the source file typically take a few seconds to a minute to appear in the destination. For time-sensitive workflows, this lag is worth factoring in.

Alternative: Static Paste for One-Time Imports

If you do not need a live connection and only need to copy data once, a static paste is simpler and does not have any of the limitations above.

  1. Copy the data from the source spreadsheet.
  2. In the destination sheet, right-click the target cell and choose Paste special > Values only.

The data lands as plain values with no formula dependency. Nothing updates if the source changes, which is exactly what you want for a one-time snapshot. For any workflow where the source data changes regularly, IMPORTRANGE is the better choice.

Troubleshooting IMPORTRANGE Errors

If IMPORTRANGE is not working, the error message in the cell is usually the fastest diagnostic. Here is what each one means and how to fix it.

Error Likely Cause Fix
#REF! Access has not been granted between the two files. Hover over the cell and click the blue Allow Access button.
#REF! after working previously Source file was moved, deleted, or sharing permissions were revoked. Confirm the source file still exists and you still have access. Re-enter the formula with the updated URL if the file was moved.
#N/A Tab name in the range string does not match the actual tab name in the source file. Check for typos and spacing. “Sheet 1” and “Sheet1” are different. Tab names are case-sensitive.
Formula Parse Error Missing quotation marks, missing parenthesis, or curved quotes instead of straight quotes. Confirm both arguments are inside straight double quotes "" and the formula closes with ). Avoid pasting URLs from apps that auto-convert to curly quotes.
Result Too Large The imported range exceeds the 10MB data limit. Wrap IMPORTRANGE inside QUERY to filter down to only the rows and columns you need before importing.
Cell shows “Loading…” indefinitely The source file is large or Google Sheets is slow to establish the connection. Wait a minute and refresh. If it persists, check that access has been granted and the source file is accessible.

Frequently Asked Questions

Is IMPORTRANGE dynamic?

Yes. When data in the source spreadsheet changes, the destination sheet updates automatically, usually within a few seconds to a minute. If you need a static snapshot that does not update, use Paste Special > Values only instead.

Can I import from multiple spreadsheets at once?

Yes, using array syntax with curly braces. To stack data from two sources vertically, use ={IMPORTRANGE("URL1","Range1"); IMPORTRANGE("URL2","Range2")}. Both ranges must have the same number of columns. Each source requires its own access grant before the combined formula will work.

Why does IMPORTRANGE keep showing a #REF! error even after I allowed access?

The most common cause after initial access is granted is that the source file was moved, deleted, or had its sharing permissions changed. Confirm you still have access to the source file. If the URL changed, update the formula with the new URL and re-grant access.

Does IMPORTRANGE import formatting as well as data?

No. Only the raw data values transfer. Cell colors, conditional formatting, text styles, checkboxes, and number formatting do not carry over. You will need to apply formatting manually in the destination sheet.

How many spreadsheets can I import from in a single file?

Google Sheets limits a single file to IMPORTRANGE connections from a maximum of 50 different source spreadsheets. If you are consolidating from many files, use QUERY to reduce import size and consider whether a Google Apps Script approach would be more appropriate at that scale.

Can I use IMPORTRANGE with VLOOKUP or QUERY?

Yes, both are common patterns. Wrap IMPORTRANGE inside VLOOKUP to use it as the lookup table, or wrap it inside QUERY to filter rows before they land in the destination. When using QUERY on imported data, use Col1, Col2, Col3 notation rather than standard column letters.

What is the difference between IMPORTRANGE and referencing another sheet with an exclamation point?

The exclamation point syntax (for example, =Sheet2!A1) references another tab within the same spreadsheet file. IMPORTRANGE is for pulling data from a completely separate spreadsheet file with its own URL. If the data you need is in a different tab of the same file, you do not need IMPORTRANGE.

What is the difference between IMPORTRANGE, IMPORTDATA, IMPORTHTML, and IMPORTXML?

All four are import functions in Google Sheets but they serve different purposes. IMPORTRANGE pulls data from another Google Sheets file using the URL of the spreadsheet. IMPORTDATA imports a CSV or TSV file from a public URL. IMPORTHTML pulls a table or list from a web page. IMPORTXML imports data from structured XML or HTML content at a URL. If you are connecting two Google Sheets files, IMPORTRANGE is the right function. If you are pulling data from a website or external file, use one of the other three depending on the format.

Does Google Sheets have an Excel equivalent for IMPORTRANGE?

Excel does not have a direct equivalent. The closest options are Power Query for importing and refreshing data from external workbooks, or manual linked cell references between Excel files saved in the same location. Neither matches the real-time cross-file usage of IMPORTRANGE in Google Sheets. If cross-file data syncing is a frequent need, it is one area where Google Sheets has a clear advantage over Excel.

Related: