What Is a #REF! Error in Excel?
If a formula suddenly shows #REF!, Excel is telling you that a reference it depends on no longer exists. It is one of the most common errors in real workbooks and one of the most disruptive, because a single deleted row can cascade #REF! through every formula that touched it. Here is what the error means and how to deal with it.
What #REF! actually means
#REF! stands for an invalid cell reference. A formula was pointing at a specific cell, range, or sheet, and that target has been removed, so there is nothing left for the formula to read. Excel replaces the lost reference with the literal text #REF! right inside the formula, which is why you will often see something like =A1+#REF! in the formula bar.
Common causes
The error usually appears right after a structural change to the workbook.
- Deleting a row, column, or worksheet that a formula referenced.
- Cutting and pasting cells over the range another formula relied on.
- Deleting cells and shifting others into their place so the original reference is gone.
- A defined name whose Refers To range was deleted, which then breaks every formula using that name.
- Pulling data from a closed workbook or a deleted external link.
How #REF! differs from other errors
It helps to tell the error apart from its neighbors. #REF! means the reference itself is broken. #NAME? means Excel does not recognize a name or function, often a typo or a deleted defined name. #VALUE! means a value is the wrong type, such as text where a number is expected. #DIV/0! means a division by zero. Knowing which one you are looking at points you straight at the cause.
How to find and clear #REF! errors
A short routine clears most of them. Also check defined names, since a #REF! hidden in the Name Manager keeps breaking formulas, and Name Scrubber finds every orphaned #REF! name and clears them in one pass, which is the cleanup people most often miss.
- Press
F5(orCtrl+G), click Special, choose Formulas, tick only Errors, and click OK. - Step through the selected cells and read each formula bar to find the
#REF!fragment. - Replace the
#REF!fragment with the correct reference, or pressCtrl+Zif the deletion just happened. - Open the Name Manager with
Ctrl+F3and delete any name whose Refers To value shows#REF!.
Name Scrubber
Filter, rename, hide, unhide, or delete defined names in bulk, and sweep out broken #REF! names in one pass.
Get ModelMint See how it worksFAQ
What causes a #REF! error in Excel?
It happens when a formula references a cell, range, or sheet that has been deleted or shifted away. Common triggers are deleting a row, column, or worksheet, or pasting over cells that other formulas relied on.
What is the difference between #REF! and #NAME? errors?
#REF! means a cell reference is broken because its target no longer exists. #NAME? means Excel does not recognize a name or function, usually a typo or a defined name that was deleted. They often appear together when a named range breaks.
How do I get rid of a #REF! error?
Find the error cells with F5 then Special then Errors, and replace the #REF! fragment in each formula with the correct reference. If it just appeared, Ctrl+Z restores the deleted reference. Also clear any defined names that show #REF! in the Name Manager.