Key Takeaways:The #REF! error means Google Sheets can't find a cell or range your formula needs.Five common causes: deleted cells, circular references, out-of-range VLOOKUP, IMPORTRANGE permission, and array collisions.Wrap formulas in IFERROR to catch #REF! before it breaks downstream calculations.Use Ctrl+H to search for "#REF!" and fix every broken reference in one pass.

You open your spreadsheet, and instead of numbers you see #REF! scattered across your sheet. Something broke, but Google Sheets doesn't tell you what. The good news: every #REF! error traces back to one of a handful of causes, and each one has a straightforward fix. This guide walks through all five causes you're likely to hit, shows you how to fix each one, and ends with a quick method for hunting down every #REF! in your workbook at once.

What Does the #REF! Error Mean in Google Sheets?

#REF! stands for invalid reference. Google Sheets throws it whenever a formula points to a cell or range that no longer exists, can't be accessed, or creates an impossible loop. Unlike #N/A (which means "no match found") or #VALUE! (which means "wrong data type"), #REF! specifically signals a broken link between your formula and the data it needs.

The error is sticky, too. If cell B2 returns #REF! and cell C2 references B2, then C2 also shows #REF!. One broken reference can cascade through an entire sheet, which is why fixing the root cause quickly matters so much. According to Google's official documentation, #REF! appears whenever "a formula refers to a cell that isn't valid."

Cause 1 -- Deleted Rows or Columns

This is the most common trigger. You (or a collaborator) delete a row or column, and a formula that pointed to a cell in that row or column instantly loses its target.

Example

Suppose cell C1 contains =A1+B1. If you delete column B, Google Sheets rewrites the formula to =A1+#REF! because the original B1 no longer exists.

How to fix it

  1. Undo immediately -- press Ctrl+Z (or Cmd+Z on Mac). This restores the deleted row or column and the references go back to normal.
  2. If undo isn't available, click on the cell showing #REF!, look at the formula bar, and replace every #REF! fragment with the correct cell address.
  3. Prevent it going forward -- use named ranges instead of raw cell addresses. Named ranges survive row and column deletions because they update automatically.

Pro tip

Before deleting rows or columns in a shared workbook, check the column or row for formulas that reference it. Select the column header, press Ctrl+F, and search for = to see if anything depends on it.

Cause 2 -- Circular Reference

A circular reference happens when a formula refers to its own cell, either directly or through a chain of other cells. Google Sheets can't resolve a value that depends on itself, so it returns #REF!.

Example

If cell A1 contains =A1+1, it's asking for its own value before it has one. Similarly, if A1 references B1 and B1 references A1, you get an indirect circular loop.

How to fix it

  1. Look at the formula bar for the flagged cell. If the formula mentions its own cell address, rewrite it to point somewhere else.
  2. Check for indirect loops -- Google Sheets shows a banner at the bottom of the screen that reads "Circular dependency detected." Click it for details.
  3. Use iterative calculation only when intentional. Go to File > Settings > Calculation and enable iterative calculation if your model genuinely requires it (for example, loan amortization schedules with circular interest).

Pro tip

If you can't spot the loop, temporarily replace the formula with a hard-coded value, then trace which cells update. That trail leads you to the circular chain.

Cause 3 -- VLOOKUP or XLOOKUP Column Index Out of Range

VLOOKUP's third argument (index) tells it which column of the lookup range to return. If that number is larger than the range has columns, you get #REF!.

Example

=VLOOKUP("Widget", A1:C10, 5, FALSE)

The range A1:C10 has only 3 columns, but the formula asks for column 5. Google Sheets can't reach a column that doesn't exist.

How to fix it

  1. Count the columns in your lookup range. Make sure the index argument is less than or equal to that count.
  2. Expand the range if the column you need exists in the sheet but wasn't included. Change A1:C10 to A1:E10, for instance.
  3. Switch to XLOOKUP if possible. XLOOKUP uses a separate return range instead of a column index, which eliminates this class of error entirely.

Pro tip

If your VLOOKUP range might grow or shrink, use COLUMNS(A1:C10) in a helper cell so you can validate the index at a glance.

Cause 4 -- IMPORTRANGE Without Permission

The IMPORTRANGE function pulls data from another Google Sheets file. The first time you use it, Google Sheets asks you to Allow access. Until you click that button, the formula returns #REF!.

Example

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

If the connected spreadsheet hasn't been authorized yet, or if the source file has been deleted or its sharing permissions changed, you'll see #REF!.

How to fix it

  1. Click the cell and look for the "Allow access" prompt. Click it once and the error resolves.
  2. If the source spreadsheet was deleted, you'll need to restore it from Trash or update the URL to a new source.
  3. Check sharing permissions -- if the owner removed your access, you'll need them to re-share the file with your Google account.

Pro tip

When you import data from external spreadsheets, wrap the IMPORTRANGE call in IFERROR to show a friendly message instead of #REF! while permission is pending: =IFERROR(IMPORTRANGE(...), "Awaiting access").

Cause 5 -- Array Formula Expanding Into Occupied Cells

An ARRAYFORMULA or a function that spills results (like FILTER, SORT, or UNIQUE) needs empty cells below or beside it to output its results. If those cells already contain data, Google Sheets returns #REF! because it can't overwrite existing content.

Example

=ARRAYFORMULA(A1:A10 * B1:B10)

This formula tries to output 10 results. If any of those destination cells already has a value, the entire formula fails with #REF!.

How to fix it

  1. Clear the cells in the output range. Delete any hardcoded values or stale data sitting below the array formula.
  2. Move the array formula to a column or area of the sheet where it has room to expand.
  3. Check for hidden content -- sometimes cells look empty but contain a space character or empty string. Select the range and press Delete to be sure.

Pro tip

Dedicate specific columns or sheets to array outputs so collaborators don't accidentally type into the spill zone.

How to Find All #REF! Errors at Once

If you suspect #REF! errors are lurking across multiple sheets, here are two fast methods to track them all down.

Method 1: Find and Replace

  1. Press Ctrl+H (Cmd+H on Mac) to open Find and Replace.
  2. Check "Search all sheets" and "Also search within formulas".
  3. Type #REF! in the search field.
  4. Click Find All. Google Sheets highlights every cell containing the error.
  5. Fix each one individually, or replace the broken fragment if you know the correct reference.

Method 2: ISERROR formula audit

Create a helper column with =ISERROR(A1) and drag it down. Every cell returning TRUE has an error. For a more targeted check, use:

=IF(ISERROR(A1), "FIX ME", A1)

This lets you scan visually for "FIX ME" labels instead of hunting for small red error text. Once everything is clean, delete the helper column.

Preventing #REF! errors when importing data

Many #REF! errors appear after importing updated CSV or Excel files that have different column structures. If your CSV data changes shape frequently, use SmoothSheet's CSV Validator to check column counts and structure before uploading. That way you catch mismatches before they break your formulas. And if your files are too large for a browser upload, SmoothSheet handles server-side CSV imports to Google Sheets without crashing your browser -- keeping your formulas intact.

Frequently Asked Questions

What is the difference between #REF! and #N/A in Google Sheets?

#REF! means a formula references a cell or range that doesn't exist or can't be reached. #N/A means a lookup function (like VLOOKUP or MATCH) searched for a value and didn't find it. In short, #REF! is a broken link problem while #N/A is a missing data problem.

Can #REF! errors fix themselves?

In one specific case, yes. If the #REF! comes from an IMPORTRANGE permission prompt, clicking "Allow access" resolves it automatically. For every other cause -- deleted cells, circular references, out-of-range indexes, or array collisions -- you need to manually edit the formula or restore the missing data.

Does IFERROR hide #REF! errors?

Yes. Wrapping a formula in =IFERROR(formula, "fallback") catches any error type, including #REF!, and returns the fallback value instead. This is useful for keeping dashboards clean, but be careful: IFERROR masks the problem rather than solving it. Always investigate the root cause before adding an IFERROR wrapper.

How do I prevent #REF! errors when collaborating?

Use named ranges instead of raw cell references. Named ranges update automatically when rows or columns shift, so collaborators can insert or delete structure without breaking formulas. Also, protect critical sheets or ranges via Data > Protect sheets and ranges to stop accidental edits.

Conclusion

Every #REF! error boils down to one thing: your formula is pointing somewhere it can't reach. Identify which of the five causes applies -- deleted cells, circular references, VLOOKUP index overflow, IMPORTRANGE permission, or array spill collisions -- fix the reference, and the error disappears. Use Ctrl+H with "Search all sheets" enabled to sweep your workbook clean in one pass, and adopt named ranges and IFERROR wrappers to make your spreadsheets more resilient going forward.