Excel REF! Error Fix
The complete Excel REF error fix: why #REF! appears, which edits break cell references, and how to repair the damage or prevent it from ever returning.
Introduction
#REF! is Excel's way of saying a formula points at a cell that no longer exists. Unlike #N/A, which is often a legitimate answer, #REF! is always structural damage: a row, column, sheet or external file has been moved or removed out from under the formula. That distinction decides the fix — you repair the reference, and you generally do not hide the error, because hiding it turns an obvious break into a silent wrong number.
Prerequisites
- Comfort editing and filling formulas in Excel
- A workbook you are allowed to restructure — or a copy of one
- Excel 2013 or later (Ctrl+G Special and ERROR.TYPE are used below)
1What #REF! Actually Means
#REF! stands for 'invalid cell reference'. Excel stores every reference as coordinates, so when the thing those coordinates pointed at disappears, the coordinates no longer resolve and Excel replaces them with #REF!. The formula keeps running with a hole in it — which is why you often see #REF! embedded inside an otherwise sensible formula, such as =SUM(#REF!, C2:C5). Contrast this with #N/A: a lookup that finds nothing is a real answer about missing data, and IFNA is an acceptable wrapper. A reference that resolves to nothing is not data about your business; it is a broken report, and it needs a repair.
Example
=B2*A2The formula in B2 multiplied by A2. Someone deleted column A, so the reference to A2 had nothing left to point at; Excel rewrote the formula as =#REF!*A2 to make the damage visible rather than silently substituting zero.
#REF! survives recalculation. It will not clear itself when the source data comes back — you have to rewrite the reference.
Diagnose the family of an error with ERROR.TYPE: it returns 4 for #REF!, 7 for #N/A and 3 for #VALUE!. Useful when auditing a sheet full of mixed errors.
If you caused the #REF! seconds ago, press Ctrl+Z immediately. Undo restores the deleted cells and Excel reconnects the formulas.
2Step 1: Find Every #REF! in the Workbook
A broken reference can sit in a cell you never look at, inside a defined name, or in a chart series, while the visible report still shows a plausible total. Locate all of them before fixing any, otherwise you will repair the one you found and ship the same bug in three other tabs.
Select all error cells at once
Use Go To Special to highlight every formula that currently returns an error, anywhere on the sheet.
Ctrl+G, then Special, Formulas, tick Errors, OKSearch the formulas directly
Find All with the search scope set to formulas lists every #REF! occurrence, including ones nested inside IFERROR fallbacks.
Ctrl+F, type #REF!, Look in: Formulas, Find AllAudit defined names
A deleted column frequently leaves a name such as Sales_Total pointing at a dead reference, which then poisons every formula that uses the name.
Ctrl+F3Check external links
References to workbooks that were moved, renamed or deleted show up here with a broken status.
Data, then Edit Links (Workbook Links in Excel 365)Example
=ERROR.TYPE(B1)ERROR.TYPE returns a number identifying the error class in a cell: 4 is always #REF!. In a column of mixed errors this lets you count the structural breaks separately from the lookup misses, so you know whether you are dealing with one deleted column or a hundred missing products.
Run the Ctrl+G search on every sheet, not just the active one. Go To Special only inspects the current sheet.
Conditional formatting rules and data validation lists can also hold dead references and will not appear in a cell-level search; review them from the Home and Data tabs.
Once you know the count, note it. The number should be zero when you finish the repair, and it is the easiest way to prove the job is done.
3Step 2: Repair the Causes That Actually Break References
Almost every #REF! traces back to one of five events: a deleted row, column or cell; a deleted worksheet; a lookup column index that runs off the end of the table; an OFFSET or INDEX that walked past the edge of the sheet; or a relative reference that slid off the grid when the formula was copied. Repair the reference, then remove the temporary #REF! placeholders. If you cannot remember the original range, use Undo, a recent backup, or the version history in OneDrive or SharePoint to see what the formula looked like before the edit.
Rewrite a missing row or column reference
Replace the #REF! token with the range it should point at. Excel leaves the token in place so you can see exactly where the hole is.
=SUM(Sheet1!A1:A10, C2:C5)Restore a deleted sheet reference
If the whole tab was deleted, either re-add a sheet with that exact name or repoint the formula at a sheet that exists.
=Sales!B2*1.2Fix an over-long column index
VLOOKUP returns #REF! when col_index_num is larger than the width of table_array. Count the columns rather than trusting memory.
=VLOOKUP($G2, $A$2:$D$500, 4, FALSE)Guard an OFFSET that leaves the grid
OFFSET returns #REF! the moment it points above row 1 or left of column A, so test the position before you move.
=IF(ROW()>1, OFFSET($A$1, ROW()-2, 0), "")Rebuild a broken external link
Repoint the path, or paste the values if the source file is gone for good.
='C:\Reports\[Sales.xlsx]Sheet1'!A1Example
=VLOOKUP($G2, $A$2:$D$500, 4, FALSE)The broken version was =VLOOKUP($G2, $A$2:$D$500, 6, FALSE), which returned #REF! because the table is only four columns wide (A to D) — there is no sixth column to return. Counting the columns in table_array turns the error into a value. The same mistake appears after a column is deleted from the middle of a table, which silently shrinks its width by one and pushes previously valid indexes out of range.
INDEX($A$2:$D$500, 10, 1) returns #REF! as soon as the row argument exceeds the range; the range is 499 rows tall here, not 500.
A relative reference can be pushed off the grid by a copy: a formula in row 1 that refers to the cell above becomes #REF! when copied, because the reference would need row 0.
Whole-column references such as A:A survive deleting rows but break if the entire column is removed. If you delete columns regularly, bound your ranges and convert the data to an Excel Table instead.
4Step 3: Prevent #REF! from Coming Back
Repairing one workbook is a good afternoon. The prevention list is what stops the next break, and it is short: convert data ranges to Excel Tables, use structured references and named ranges so formulas refer to meanings rather than coordinates, and treat INDIRECT with suspicion because it resolves references from text at calculation time and turns a typo into #REF! with no warning.
Convert the range to a Table
A Table expands automatically as rows are added, so new data is inside the formula's range without any edit.
Ctrl+TUse structured references
Column names replace coordinates, which makes the formula readable and stable when columns are inserted.
=SUM(Sales[Amount])Guard dynamic references built from text
INDIRECT returns #REF! if the sheet name or address it assembles is wrong, so validate it and supply a fallback.
=IFERROR(INDIRECT("'"&$B$1&"'!A1"), "Check sheet name")Prefer INDEX to OFFSET where you can
OFFSET is volatile and easy to aim off the sheet; INDEX takes a fixed frame and simply fails loudly when the position is out of range.
=INDEX($A$2:$D$500, 10, 2)Example
=IFERROR(INDIRECT("'"&$B$1&"'!A1"), "Check sheet name")INDIRECT cannot tell you that B1 contains a typo — it just hands Excel a reference that does not resolve and #REF! appears. Wrapping it with IFERROR converts an opaque structural error into a message a colleague can act on. Note that a name typed in B1 must match the real tab name exactly, and tabs with spaces need the single quotes this formula already adds.
Excel Tables auto-expand for new rows and tolerate inserted columns, but deleting a column that a structured reference names still produces #REF! — rename or repoint before deleting.
Named ranges defined with a fixed address (Name Manager, Ctrl+F3) are safer than raw ranges because every dependent formula shows a meaningful name when it breaks.
Before you delete a sheet or a column that feeds reports, run Find All on the sheet name or the column reference to see what depends on it.
Keep a copy of the workbook before large structural edits. Ctrl+Z only reaches back so far, and version history is only available for files stored in OneDrive or SharePoint.
5Why IFERROR Is the Wrong Fix Here (and What to Use Instead)
The tempting one-liner is to wrap everything in IFERROR and make the error disappear. Resist it for #REF!. IFERROR catches all eight error types, so it will also swallow #VALUE!, #DIV/0! and #N/A, and it converts a reference you can repair into a blank cell that quietly understates a total. #REF! is never a legitimate 'no data' answer — it is a claim that the formula is pointed at nothing. Fix the reference first; only if the break is genuinely unavoidable, and you have documented why, use IFERROR with a sentinel that stays visible.
Example
=IFERROR(B2*A2, "CHECK REFERENCE")The formula still fails, but it fails loudly and in words. Compare that with =IFERROR(B2*A2, ""), which returns an empty cell: the column then sums to a number that looks perfectly reasonable and is wrong. When an audit is what you need, ERROR.TYPE and ISREF are more useful than a wrapper, because they tell you what kind of failure you have.
Match the wrapper to the error: IFNA for lookups that may legitimately miss, and no wrapper at all for #REF!, which you repair.
ISREF tells you whether a value is still a valid reference, which is handy when auditing formulas that build their targets dynamically.
Before you ship a workbook, set the error count to zero with Ctrl+G, Special, Formulas, Errors, then compare the count against the number you recorded before the repair.
A blocked dynamic array shows #SPILL!, not #REF!, even though both look like layout problems — clear the cells the array needs to spill into.
Functions Used
IFERROR
IFERROR returns a custom result when a formula generates an error, and the formula result otherwise.
OFFSET
OFFSET returns a reference that is a specified number of rows and columns from a starting cell or range.
INDIRECT
INDIRECT converts a text string into a cell reference that Excel can use in a formula.
INDEX
INDEX returns the value at a specified position in a range or array.
Related Guides
Excel N/A Error Fix
The complete Excel N/A error fix guide: why #N/A appears in VLOOKUP and XLOOKUP, how to repair the root cause, and when to wrap it with IFNA or IFERROR.
Excel VALUE! Error Fix
The #VALUE! error means Excel expected a number but found text. Learn how to fix an excel value error fix fast using IFERROR, VALUE, and clean data checks.
Google Sheets IMPORTRANGE
Google Sheets IMPORTRANGE pulls live data from another spreadsheet into yours. Learn the syntax, how to grant access, and how to fix the #REF! access error.
Summary
#REF! means a formula points at a cell that no longer exists, and it is always structural damage rather than a legitimate answer. Find every instance first with Go To Special, Find All set to formulas, and a check of defined names and external links. Then repair the five common causes: deleted rows or columns, deleted sheets, a VLOOKUP column index wider than its table, an OFFSET or INDEX that left the grid, and relative references pushed past the sheet edge. Stop it recurring by converting ranges to Excel Tables, using structured references and named ranges, guarding INDIRECT against bad text, and preferring INDEX to OFFSET. Finally, do not paper over #REF! with IFERROR — fix the reference, or use a visible sentinel, and verify the error count is back to zero.
Next Steps
- Run Ctrl+G, Special, Formulas, Errors on each sheet and write down how many #REF! cells you have
- Repair them one cause at a time, using Ctrl+Z or version history to recover the original range where it is unclear
- Convert your main data ranges to Excel Tables with Ctrl+T so inserted rows no longer break the formulas that read them