Knowing how to fix the #REF! error in Excel saves a lot of frustration when a working spreadsheet suddenly fills with broken formulas. The error appears when a formula points to a cell, row, column, or sheet that no longer exists or can no longer be reached. The good news is that the cause is almost always traceable, and the repair takes only a few minutes.
Key Takeaways
- #REF! means a formula has lost the cell it was pointing to, usually after a deletion or a cut-and-paste.
- Pressing Ctrl+Z right after the error appears is the fastest fix.
- Find and select every affected cell using Find or Go To Special, then rewrite the broken reference.
- Lookup functions, defined names, and linked workbooks can also trigger the error and need separate checks.
How to Fix the #REF! Error in Excel Step by Step
Undo the Change That Caused the Error
When #REF! shows up immediately after deleting a row, column, or sheet, or after pasting over cells, the quickest repair is to reverse the action.
- Step 1: Press Ctrl+Z (Cmd+Z on Mac) right after the error appears. Repeat until the formulas return to normal. The original references are restored automatically.
- Step 2: If the file has already been saved and closed, open it from OneDrive or SharePoint, go to File, then Info, then Version History, and open an earlier version from before the deletion. Copy the correct data back into the current workbook.
Tip: Undo only works within the current session and stops working once the workbook is closed, so test the result before saving over the file.
Find and Repair the Broken References
If undoing is no longer possible, the broken formulas have to be located and rewritten. Think of it like a street address that no longer exists: the formula still knows where it was meant to go, but the destination is gone.
- Step 3: Press Ctrl+F to open Find and Replace. Type #REF! in the Find what box, then click Options.
- Step 4: Set Within to Workbook and Look in to Formulas. Click Find All. Excel lists every cell whose formula contains #REF!, so nothing gets missed on other sheets.
- Step 5: Click the first result in the list, then look at the formula bar. The broken part appears as #REF! in place of a normal cell address, for example =SUM(A2:#REF!) or =#REF!*B2.
- Step 6: Replace the #REF! portion with the correct cell or range. Click inside the formula bar, select #REF!, then click the right cell on the sheet, or type the address directly. Press Enter.
- Step 7: Copy the repaired formula down or across to any matching cells that show the same error. Use the fill handle in the bottom-right corner of the cell, or press Ctrl+D to fill down.
- Step 8: Run Find All again for #REF!. When the search returns no results, every broken reference has been fixed.
Tip: To jump straight to every error cell on a sheet, press F5, click Special, choose Formulas, tick only Errors, and click OK. This selects all cells showing any error, which helps when many formulas are affected.
Check Names, Lookups, and Links That Hide the Problem
Sometimes the cell itself looks fine but the error comes from somewhere else. These checks cover the usual culprits.
- Step 9: Go to the Formulas tab and click Name Manager. Look at the Refers To column for any name that contains #REF!. Select the name, click in the Refers To box, enter the correct range, and confirm with the check mark. Delete the name if it is no longer needed.
- Step 10: For a VLOOKUP formula, check that the column index number is not larger than the number of columns in the table array. A table covering A:C cannot return column 4. Lower the number or widen the range.
- Step 11: For INDEX formulas, confirm that the row and column numbers fall inside the selected range.
- Step 12: If the formula references another workbook, open that source file first. Then go to the Data tab, click Edit Links, and use Check Status or Change Source to point the link at the correct file.
Common Problems and Fixes
The Error Returns After the Formula Is Fixed
This usually means the same formula was copied to other cells before the repair, or another formula depends on the broken one. Run Find All for #REF! again and fix each remaining result. Check dependent cells with Formulas, then Trace Dependents.
The Formula Bar Shows Only #REF! With No Visible Reference
The whole reference was deleted, so there is no address left to repair. Click Formulas, then Evaluate Formula, to step through the calculation and see which part fails. Then rebuild the formula using the correct cells.
Find Does Not Locate the Error
The Look in option is probably set to Values instead of Formulas, or Within is limited to the current sheet. Reopen the Find options, set Look in to Formulas and Within to Workbook, and search again.
The Error Appears Only After Opening a File on Another Computer
The workbook likely links to an external file stored at a path that does not exist on that computer. Open Data, then Edit Links, and update the source location, or break the link to keep current values.
A Pasted Range Shows #REF! in Every Cell
Cutting and pasting over cells that other formulas rely on removes those references. Undo the paste, then use Copy instead of Cut, or paste into a different location.
Frequently Asked Questions
What does the #REF! error mean in Excel?
It means a formula refers to a cell, range, or sheet that is no longer valid. The target may have been deleted, overwritten, or moved in a way that breaks the link.
What is the fastest way to fix a #REF! error?
Press Ctrl+Z immediately after the error appears. This reverses the deletion or paste that caused it and restores the original references.
Can a #REF! error be fixed after the file is saved and closed?
Undo no longer works at that point, so the broken part of each formula must be rewritten with the correct reference. On OneDrive or SharePoint, an earlier version can also be restored through File, Info, Version History.
How do I find every #REF! error in a workbook?
Press Ctrl+F, enter #REF!, click Options, set Within to Workbook and Look in to Formulas, then click Find All. Excel lists every cell that contains the error.
Why does VLOOKUP return #REF!?
The column index number is larger than the number of columns in the table array. Reduce the number or extend the lookup range so it includes the column being requested.
Why do deleted rows or columns cause #REF!?
Formulas store the addresses of the cells they use. Once a referenced row or column is deleted, the address no longer exists, so Excel replaces it with #REF! in the formula.
Can a defined name cause a #REF! error?
Yes. If the range behind a name was deleted, the name shows #REF! in the Refers To box of Name Manager, and any formula using that name returns the error. Correct or delete the name there.
Does a closed linked workbook cause #REF!?
It can, particularly with functions such as INDIRECT that cannot read from a closed workbook. Open the source file, or update the link under Data, then Edit Links.
Is IFERROR a real fix for #REF!?
No. IFERROR only hides the message and leaves the broken reference in place. The reference itself still needs to be repaired for the formula to return correct results.
