Fix guide

How to fix the #REF! error in Google Sheets™

The #REF! error means your formula is pointing at a cell, range, sheet, or named range that no longer exists. There are four common causes. Here is the fix for each, plus how to make sure you never miss the one that hides in the formula bar.

What #REF! means

When Google Sheets™ cannot resolve a reference inside a formula, it replaces the broken reference with the literal string #REF! and the cell shows the same error. The formula text itself is rewritten:

Before: =SUM(F2:F100) After: =SUM(#REF!)

Once the reference is replaced by #REF!, the original column letter is gone from the formula. That is the trap: you cannot recover what column it pointed at by inspecting the formula, only by undoing the action that broke it or by checking version history.

The four causes (and the fix for each)

Cause 1 — You deleted a column or row a formula was using

Most common. Someone deletes column F because it "looks unused" and now =SUM(F2:F100) across the workbook becomes =SUM(#REF!).

Fix: press Ctrl+Z (or Cmd+Z) immediately to undo. If the delete is older, open File › Version history › See version history, find a version before the delete, and either restore it or copy the lost data back. If the data is genuinely gone and the formula is supposed to point at the new layout, rewrite the reference manually.

Cause 2 — A VLOOKUP column index exceeds the search range

This one is easy to miss because the formula looks valid:

=VLOOKUP(A2, Products!A:C, 4, FALSE)

The range Products!A:C has only three columns. The 4 asks VLOOKUP to return the fourth, which does not exist. Sheets returns #REF! for every match.

Fix: either widen the range (e.g. Products!A:D) or change the column index to a value that exists. If you are also seeing #N/A for non-matches, you have two bugs in the same formula.

Cause 3 — A referenced sheet was deleted or renamed without updating formulas

You had ='Q1 Data'!A2 across a model. Someone deleted the "Q1 Data" tab or renamed it to "Q1 - Final". The reference cannot resolve, and you get #REF! in every dependent cell.

Fix: if it was renamed, do a sheet-wide Find & Replace (Ctrl+H) with "search within formulas" enabled. Replace the old sheet name with the new one. If it was deleted, restore from version history or rebuild the data.

Cause 4 — A named range was deleted

Less common but harder to spot. A formula like =SUM(Revenue) looks fine, but if the "Revenue" named range was removed (Data › Named ranges), it returns #REF!.

Fix: open Data › Named ranges, recreate the range pointing at the correct cells, and the formula resolves immediately.

How to find every #REF! in the workbook

  1. Press Ctrl+F on Windows or Cmd+F on Mac.
  2. Click the three-dot menu in the dialog for advanced options.
  3. Check "Also search within formulas".
  4. Type #REF! into the search box.
  5. Cycle through every hit with Enter. Fix in place.

This catches every visible #REF!. The harder problem is the formulas that used to reference a deleted column but no longer show #REF! because Sheets auto-rewrote them — those return wrong numbers silently.

The auto-rewrite trap: when Google Sheets deletes a column, formulas that referenced it sometimes get rewritten to #REF!, but range-based formulas like =SUM(B:F) may shift instead — F becomes the column that was previously in G. The formula still computes a number; just the wrong one. Find & Replace cannot detect this case, and SheetBrain's Check tab does not target it either. The shift case is what SheetBrain's Repair tab is built for: it compares the active sheet to a captured schema snapshot and proposes safe formula rewrites. Repair is part of the $9/mo Pro plan.

How to prevent #REF! from breaking reports

  • Do not delete columns without checking dependencies. Right-click the column, choose "Show formulas" via the View menu first, search for the column letter in formulas across all tabs.
  • Wrap critical lookups with IFERROR so a broken reference does not propagate as #REF! through 30 downstream formulas: =IFERROR(VLOOKUP(...), "").
  • Use named ranges instead of letter references for important data ranges. Named ranges break visibly if deleted and survive column inserts.
  • Run a deterministic Check after structural edits. SheetBrain's Check tab (Detector Set v0.4) flags formula cells currently displaying common errors, including #REF!, plus lookup formulas with column or row indexes that exceed their referenced range, formula pattern outliers, and missing copied formulas — free, no AI quota, unlimited scans. For the post-delete column-shift case, use Repair.

Find every visible broken reference

SheetBrain's Check tab runs a free deterministic audit for visible formula errors, #REF!, mixed types, duplicate lookup keys, formula pattern outliers, and missing copied formulas. The post-delete column-shift case is handled separately by Repair.

Install free on Google Workspace

TL;DR

  • #REF! = a formula is pointing at a cell / range / sheet / named range that no longer exists.
  • Four common causes: deleted column, bad VLOOKUP index, deleted/renamed sheet, deleted named range.
  • Find them all: Ctrl+F, enable "search within formulas", search for #REF!.
  • Fix path is usually undo, version history, or rewrite. Wrap critical lookups in IFERROR to limit blast radius.
  • The silent post-delete column-shift case is what manual auditing misses. SheetBrain's Repair tab handles that with a snapshot-and-rewrite flow; the Check tab covers visible formula errors, formula pattern outliers, missing copied formulas, and lookup formulas with out-of-range indexes. More on the v0.4 Check detectors.

Related