How to Fix a #REF! Error in Google Sheets
For when a cell suddenly shows #REF! and you're not sure what you broke — or how to get the number back without breaking three more things.
On this page
A #REF! error looks alarming, but it's one of the clearest messages a spreadsheet gives you: a formula is trying to use a cell, range, or sheet that no longer exists.
#REF! almost always means you (or a collaborator) deleted a row, column, or tab that a formula depended on. Press Undo (Ctrl/Cmd + Z) first — if the deletion was recent, that's the cleanest fix. If undo isn't an option, find the broken reference inside the formula and point it at the correct cell, then check version history to confirm what changed.
Quick diagnosis#
| Symptom | Likely cause | First fix |
|---|---|---|
#REF! appeared right after you deleted something | A deleted row, column, or tab a formula used | Undo immediately |
The formula text itself contains #REF! | The reference was permanently lost | Edit the formula to point at the right cell |
#REF! after pasting | The paste landed where it had nowhere valid to refer | Undo, then paste values instead |
#REF! from an IMPORTRANGE | The source sheet, tab, or range moved | Re-check the source URL and range |
Why it happens (in plain English)#
When you write =SUM(B2:B10), the formula is pointing at real cells. If you then delete column B, the formula has nothing left to point at — so instead of guessing, Sheets shows #REF!. The same thing happens if you delete a tab that a formula references, or remove rows inside a range a lookup depends on.
It is not random, and it's not a bug. Something the formula needed was removed.
How to fix it, safest first#
- Undo (Ctrl/Cmd + Z). If the break just happened, this restores the deleted thing and the formula recovers instantly. This is almost always the right first move.
- Read the formula. Click the cell and look for
#REF!inside the formula, e.g.=#REF!+C2. That tells you exactly which part lost its reference. - Re-point the reference. Replace the
#REF!fragment with the correct cell or range. If a total used to sumB2:B10, point it back at wherever that data lives now. - Check
IMPORTRANGEsources. If the error is in an imported range, the source file, tab name, or range probably changed. Confirm the URL and the exact range string. - Use version history. Open it to see what changed and when — you can inspect or restore an earlier version if a bad edit went unnoticed.
Don't do this
Don't rebuild the whole sheet because one cell shows #REF!. And don't keep deleting columns to "clean up" while formulas still depend on them — that just spreads the error. Fix the one broken reference, or undo the deletion.
What not to change#
| Leave it alone | Why |
|---|---|
| Other working formulas | They're fine; only the #REF! cell lost a reference |
| Protected ranges | They may be the thing keeping your dashboard intact |
The source tab of an IMPORTRANGE | Renaming it again will break more imports |
How to stop it happening again#
The best prevention is structural, not clever. Three habits do most of the work:
- Keep inputs, lookup lists, and formulas on separate tabs. A cleanup on one tab then can't quietly delete a cell another tab relies on.
- Protect formula and dashboard ranges. This reduces accidental deletes — though it's a guardrail, not security.
- Before deleting any column, check it first. Glance at whether a formula totals or looks up from it.
Build it so it's hard to break
A workbook that separates inputs, lookups, and reporting tabs is far more resistant to #REF! in the first place. The small-business setup guide shows that structure step by step.
When to rebuild instead of fix#
If a sheet is throwing #REF! across dozens of cells because of a big structural change, it can be faster to start from a clean copy of a known-good version (via version history) than to chase every broken reference. For sheets you rely on weekly, that's a sign to add a little structure and a change log so the next cleanup is safer.
Questions people ask
Will undo always fix a #REF! error?
What's the difference between #REF! and #N/A?
Why does IMPORTRANGE show #REF!?
We aim to keep this accurate and date-stamp it when product steps change. We don't claim hands-on product testing we haven't done, and we'll always point to the free, native route first. How we work.