Google Sheets #REF! Error: Formula Fix Guide

What Does This Error Mean?

The #REF! error in Google Sheets means a formula is pointing to a cell or range that no longer exists. This almost always happens when you delete a row, column, or sheet that a formula was using. You need to update the formula to point to a valid cell range.

Affected Models

Google Sheets Web
Google Sheets iOS App
Google Sheets Android App
Google Workspace
All Google accounts

Common Causes

A row or column that the formula referenced was deleted
A formula was copied and the relative references moved outside the valid range
A named range that the formula used was deleted or renamed
A sheet that the formula referenced across tabs was deleted or renamed
A circular reference is causing Google Sheets to report an invalid reference

How to Fix It

Click the cell showing #REF! and look at the formula bar to see which reference is highlighted in red.

Google Sheets highlights the broken reference in red inside the formula, making it easy to spot.
Press Ctrl+Z (Undo) immediately if you just deleted a row or column — this restores the deleted data.

Undo works instantly and is the fastest way to fix an accidental delete.
If undo is not possible, manually edit the formula to replace the red #REF! part with the correct cell or range.

Click inside the formula bar, delete the red text, and type the valid cell address (for example, A2:A10).
If the formula was copied across rows and broke, re-anchor it using dollar signs — for example, change A1 to $A$1.

Dollar signs lock the reference so it does not shift when the formula is copied.
Check Edit > Find and Replace, search for #REF! to find all affected cells across the whole sheet at once.

This is a fast way to locate every broken reference when multiple cells are affected.

When to Call a Professional

The #REF! error is a formula issue you can fix yourself — no professional help is needed. If your spreadsheet is very complex and you cannot find the broken reference, consider Google Workspace support at support.google.com/docs.

Frequently Asked Questions

Can I recover a deleted row that caused #REF!?

Yes — use Ctrl+Z to undo the deletion immediately. If you cannot undo, check File > Version history > See version history to restore a previous version of the spreadsheet.

Why did #REF! appear after I copied a formula?

When you copy a formula, Google Sheets automatically adjusts cell references relative to the new position. If the adjusted reference ends up outside the spreadsheet bounds (like row 0 or a negative column), it shows #REF!. Use dollar signs to lock the reference before copying.

Does #REF! affect other cells in my sheet?

Yes — any other formula that depends on the cell showing #REF! will also show #REF! or an error. Fix the original broken cell first, and the dependent cells will usually correct themselves automatically.