Fix the #REF! error

Excel & Google Sheets

What #REF! means

When IMPORTRANGE is used for the first time between two Google Sheets files, Sheets requires explicit permission before it will read data from the source spreadsheet. Until that permission is granted, every cell using that IMPORTRANGE returns #REF! with the message 'You need to connect these sheets'. This is a one-time authorization step per source spreadsheet URL.

Common causes

The IMPORTRANGE formula was just entered for the first time and the authorization dialog has not been dismissed yet.
The source spreadsheet was shared with a different Google account than the one currently viewing the destination sheet.
The source spreadsheet's sharing settings were changed (e.g., from Anyone with link to Restricted) after authorization was previously granted.
The formula uses an incorrect spreadsheet URL or spreadsheet ID, so Sheets cannot find the source to prompt for access.
The IMPORTRANGE was copied from another file that had authorization; the new file needs its own separate authorization.

Example fix

Broken

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/WRONG_ID","Sheet1!A1:D100")

Fixed

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/CORRECT_ID","Sheet1!A1:D100")

Replaced the incorrect spreadsheet ID in the URL. After fixing the URL, click the cell and click 'Allow access' to complete authorization.

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

Cell row 1 column 1	Cell row 1 column 2
Cell row 2 column 1	Cell row 2 column 2
Cell row 3 column 1	Cell row 3 column 2
Cell row 4 column 1	Cell row 4 column 2

Formula

Runs server-side · free · no signup

How to fix it

1Click the cell showing #REF!. If authorization is pending, a 'Allow access' button appears in the tooltip. Click it.
2Verify the spreadsheet URL in the formula is correct and points to an accessible file — open the URL in a browser to confirm.
3Make sure you are signed in to the Google account that has at least viewer access on the source spreadsheet.
4If sharing settings on the source changed, ask the source owner to re-share it with your account, then re-authorize.
5Authorization is per source file per destination file. If you copy the destination file, you must re-authorize IMPORTRANGE in the copy.

Stop hunting errors by hand.

Upload your spreadsheet and the Auditor flags every #REF! and broken formula at once — or paste this one formula and get the fix explained.

Frequently asked

I clicked Allow access once — why does #REF! keep coming back?

Re-authorization is required when the source file's sharing settings tighten, when the destination file is copied, or when the source URL changes. Click Allow access again in those situations.

Can I use IMPORTRANGE with a private Google Sheet that is not shared with me?

No. IMPORTRANGE requires at least viewer access to the source spreadsheet. Ask the owner to share the file with your Google account.

Does IMPORTRANGE work in Excel?

No. IMPORTRANGE is a Google Sheets-only function. In Excel, use Power Query or a direct workbook link to pull data from an external file.

Related formulas

IMPORTRANGE QUERY (Google Sheets)IFERROR FILTER

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026