Microsoft Excel — #REF! Error — What It Means & How to Fix It

What Does This Error Mean?

The #REF! error in Excel means a formula is pointing to a cell that no longer exists. This usually happens when you delete a row, column, or sheet that a formula was referencing. Excel cannot find the data it was told to use — so it shows #REF! instead of a result. The good news: it is almost always fixable by updating the formula to point to the correct cell.

Affected Models

Microsoft Excel 2016
Microsoft Excel 2019
Microsoft Excel 2021
Microsoft 365 Excel

Common Causes

A row or column was deleted that contained a cell referenced by a formula
A worksheet was deleted that another formula was pulling data from
A formula was copied and pasted into a position where the relative references shift outside the valid cell range
A VLOOKUP or INDEX formula has a column index number larger than the number of columns in the selected range
A named range was deleted or renamed but the formula still uses the old name

How to Fix It

Press Ctrl + Home to go to the top of the spreadsheet, then press Ctrl + F to open Find. Type #REF! in the search box and click Find All. Excel will list every cell containing the error.

This is the fastest way to locate all #REF! errors in a large spreadsheet at once.
Click on a cell showing #REF! and look at the formula bar. You will see the formula with the word REF inside it — for example, =SUM(A1:#REF!). This tells you exactly where the broken reference is.

The word REF in the formula marks the exact spot where a deleted or invalid cell reference used to be.
If you just deleted something, press Ctrl + Z immediately to undo the deletion. Then re-check the formula to see if the error clears. If it does, rethink your deletion — move the data somewhere else before deleting.

Undo works best immediately after the deletion. If you have done other things since then, undo may not be safe.
If you cannot undo, click the broken cell and manually retype the formula with the correct cell reference. Replace the #REF! portion with the actual cell address you meant to point to.

For example, if the formula shows =B2/#REF!, you might correct it to =B2/C2 — wherever the intended data now lives.
If you copied a formula and it broke, try pasting as values only instead. Press Ctrl + C to copy, then right-click the destination, choose Paste Special, and select Values. This removes formulas entirely and just keeps the numbers.

Paste as values is a safe option when you do not need a live formula — you just want the result without it recalculating.

When to Call a Professional

The #REF! error is a formula issue, not a sign that your data is lost. If you have a large, complex spreadsheet with hundreds of formulas and cannot track down the source, a spreadsheet consultant or Excel-experienced colleague can audit the formulas for you. For business-critical financial models, it is worth having someone else review the fix before you rely on the numbers.

Frequently Asked Questions

Will the #REF! error delete my data?

No — #REF! is a formula display error, not data loss. Your underlying data in other cells is fine. Only the cell showing #REF! is affected, and that is because its formula is broken. Fix the formula and your data will show correctly again.

I see #REF! in hundreds of cells. Do I have to fix them one by one?

Not necessarily. If they all have the same broken formula pattern, you can use Find & Replace. Press Ctrl + H, search for the broken reference (like /#REF!), and replace it with the correct reference. Be careful — preview each replacement before doing a bulk replace to make sure you are fixing the right thing.

My formula worked fine and then I moved some cells — now I get #REF!. Why?

Excel uses relative references by default. When you move cells rather than copy them, it can shift formula references unexpectedly. Try pressing Ctrl + Z to undo the move, then cut and paste the cells differently. Alternatively, use absolute references (add $ signs — like $A$1) before moving, so the formula always points to a fixed address.