Excel’s #REF! error means a formula points to a cell or range that Excel can no longer identify.
Learn how to find the broken reference, repair it safely, and prevent the error from returning.
What does the #REF! error mean in Excel?
#REF! is Excel’s invalid-reference error.
It appears when a formula refers to a cell, range, worksheet, or workbook location that no longer exists or cannot be resolved.
The error can appear by itself or inside a larger formula, such as =SUM(#REF!) or =A2*#REF!.
Unlike #VALUE!, which usually indicates an incompatible value or operation, #REF! points to a broken address.
The formula may have been valid earlier, but an edit changed or removed something it relied on.
Common causes of an Excel #REF! error
- Deleting referenced cells: If a formula uses
=SUM(B2:B10)and those cells are deleted, Excel may replace the missing reference with#REF!. - Cutting and moving cells: Moving a referenced range with Cut and Paste can disrupt some formulas, especially when the move crosses sheets or workbooks.
- Removing a worksheet: Formulas that refer to a deleted worksheet can become invalid.
- Editing lookup formulas: In
VLOOKUP, the column index must be within the selected table range.
A request for a column outside that range can return#REF!. - Using an invalid index: Functions such as
INDEXcan return#REF!when the requested row or column lies outside the array. - Breaking a link: A formula may rely on an external workbook that was moved, renamed, or disconnected.
How to fix an Excel #REF! error
1. Inspect the formula and restore the intended reference
Select the cell showing the error and review the formula bar.
Find each occurrence of #REF! and determine which cell, range, sheet, or workbook the formula was meant to use.
Replace the broken part with a valid reference, then check the result against the underlying data.
For example, if the intended total is in cells B2:B10, replace a damaged formula such as =SUM(#REF!) with =SUM(B2:B10).
If the correct range is on another worksheet named Sales, use =SUM(Sales!B2:B10).
2. Undo a recent deletion or move
If the error appeared immediately after deleting or moving cells, press Ctrl+Z to undo the edit.
Do this before making more changes, because Undo may not recover the original formula after the workbook has been closed or the undo history has been replaced.
When undoing is not possible, restore the missing cells or worksheet from a saved copy, version history, or backup.
Then update the formula to point to the restored location or to the correct replacement range.
3. Check the worksheet and workbook names
For a sheet reference, confirm that the named sheet exists and that the formula is using the intended tab.
Sheet names containing spaces or punctuation need single quotes around the name, as in ='Quarter 1'!B4.
If an external workbook is involved, check whether the file was renamed or moved and use Excel’s link-management options to update the source where available.
4. Correct the VLOOKUP column index
VLOOKUP returns #REF! if its column index is greater than the number of columns in the table array.
For example, =VLOOKUP(E2,A2:C20,4,FALSE) asks for the fourth column, but the array A2:C20 contains only three columns.
If the desired result is in column C, use index 3: =VLOOKUP(E2,A2:C20,3,FALSE).
Some Excel versions include XLOOKUP, which identifies the lookup and return ranges separately and avoids a numeric column index.
A compatible alternative for older versions is INDEX with MATCH, provided the row and column positions stay within the selected ranges.
5. Check INDEX row and column numbers
In INDEX(array,row_num,[column_num]), the requested row or column must exist in the selected array.
If A2:C10 is the array, it has nine rows and three columns.
A request for row 10 or column 4 is outside its bounds and can return #REF!.
For example, =INDEX(A2:C10,3,2) returns the value at the third row and second column of that array, which is cell B4.
Check that any calculated row or column number stays within the array dimensions.
Find #REF! errors across a workbook
To locate formulas containing the error text, use Excel’s Find feature and search for #REF!.
In the search options, look in formulas if you want to find broken references stored in formulas, rather than only cells currently displaying the error.
Search the entire workbook when the option is available, since the affected formula may be on another worksheet.
You can also use Home > Find & Select > Go To Special > Formulas and select errors to locate cells returning error values.
This method can find several kinds of formula errors, so inspect each selected cell to confirm it is a reference problem.
Can IFERROR hide a #REF! error?
IFERROR can replace an error result with a message or alternate value, but it does not repair the underlying reference.
For example, =IFERROR(VLOOKUP(E2,A2:C20,4,FALSE),"Check formula") displays a message instead of #REF!.
The invalid column index remains, so fix the lookup range or index before relying on error handling.
Use error handling when an error is an expected outcome and the fallback is meaningful.
Avoid wrapping a complex formula in IFERROR just to make the worksheet look clean: it can hide broken references and other problems that need attention.
Prevent future #REF! errors
- Update formulas before deleting source rows, columns, ranges, or worksheets.
- Use Excel Tables for expanding datasets; structured references can adjust as table rows are added or removed.
- Review formulas that depend on external workbooks before renaming or moving those files.
- Use named ranges for important inputs when clear names make formulas easier to maintain.
- After restructuring a workbook, search formulas for
#REF!and check key totals and lookup results.
Excel and Google Sheets compatibility
Excel and Google Sheets both display #REF! for invalid references, and the same core checks apply: verify that the referenced cells, ranges, and sheets exist, and confirm that lookup or index arguments fit their ranges.
Function availability and workbook-link behavior can vary by application and version.
XLOOKUP is available in current Excel versions and Google Sheets, but may not be supported in older Excel editions; use a compatible lookup formula when sharing a workbook with people on older versions.