Why does IMPORTRANGE show #REF!?

Usually because access has not been allowed yet. Hover over the cell and click Allow access. You need at least view access to the source.

Is there a size limit?

Yes. Google documents a limit of 10 MB of data per request, so very large ranges may fail.

Can IMPORTRANGE pull from an Excel file in Drive?

It works with native Google Sheets files. Convert the Excel file to Google Sheets first.

What does IMPORTRANGE do in Google Sheets?

Google Workspace glossary Google Workspace courses

Quick answer

IMPORTRANGE is a Google Sheets function that pulls a range of cells from another spreadsheet into the current one and keeps it in sync. It takes the source spreadsheet’s URL and a range, for example =IMPORTRANGE("https://docs.google.com/spreadsheets/d/…", "Jobs!A1:E200").

Last updated October 22, 2026

Updated October 22, 2026 · By Robert Breen

Why it matters

Businesses end up with several sheets: one per crew, per location or per salesperson. Copying them into a master sheet every week is slow and goes stale. IMPORTRANGE links them, so the master always reflects the latest numbers.

It also lets you share a summary without sharing the raw data. The master can pull a few columns from a sheet that most people have no access to.

A worked example

A bakery with three shops keeps one sales sheet per shop. The owner creates a master sheet with three IMPORTRANGE formulas, one per shop, each pulling the Daily sales tab, and a SUMIF table underneath that totals by product across all three.

The first time, each formula shows #REF! with an "Allow access" button; she clicks it once per source. From then on, when a shop manager enters Tuesday’s sales, the master updates without anyone copying a cell.

Often confused with

IMPORTRANGE vs copy and paste

Pasted data is a snapshot. IMPORTRANGE stays linked and updates when the source changes.

IMPORTRANGE vs a sheet reference

A reference like Jobs!A1 points to another tab in the same file. IMPORTRANGE reaches into a different file.

IMPORTRANGE vs an automation

An automation tool can copy rows between sheets on a schedule or trigger. IMPORTRANGE is a live formula with no extra tool. The AI glossary page on Google Sheets covers using Sheets with automations.

Tips

Import only the columns and rows you need; large imports slow the sheet.
Put the URL in a cell and refer to it, so it is easy to update.
Keep imported data on its own tab and build summaries on another.

SUMIF Sharing permissions (Viewer, Commenter, Editor)Filter view (Google Sheets)Smart Fill (Google Sheets)

Learn it in a free lesson

Google Sheets Tutorial: Build a Job Tracker That Totals Itself (about 15 minutes)

Related in the AI glossary

Google Sheets (as an automation database)

Sources

Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.

Google Docs Editors Help: IMPORTRANGE

Frequently asked questions

Why does IMPORTRANGE show #REF!?: Usually because access has not been allowed yet. Hover over the cell and click Allow access. You need at least view access to the source.
Is there a size limit?: Yes. Google documents a limit of 10 MB of data per request, so very large ranges may fail.
Can IMPORTRANGE pull from an Excel file in Drive?: It works with native Google Sheets files. Convert the Excel file to Google Sheets first.

All google workspace terms, A to Z · Free google workspace lessons