#REF! means a formula points to something that no longer exists: a deleted cell, row, column, sheet, or named reference. Excel cannot infer the missing target, so it shows the reference error.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Google Sheets when a formula breaks after deleting or moving spreadsheet structure.
Undo immediately
This is the easiest fix if you catch the error right away.
- Press Ctrl+Z on Windows or Cmd+Z on Mac.
- Repeat until the deleted row, column, sheet, or range returns.
- Check that formulas display results again instead of #REF!.
- Save the repaired file.
Undo works only in the current editing session and before too much unrelated work makes undo unsafe.
Fix it manually when Undo is not available
Once the file is saved and closed, you need to rebuild the missing reference.
- Click a cell showing #REF!.
- Read the formula bar and note where #REF! appears.
- Use a backup, Microsoft 365 Version History, or a coworker copy to identify the original reference if possible.
- Replace #REF! with the correct current cell, range, sheet, or name.
- Use Ctrl+H and search within Formulas for #REF! to locate other broken formulas.
Excel does not keep a reliable memory of what the deleted reference used to be, so a backup or domain knowledge is often required.
More control
Use sturdier formulas
Hardcoded offsets and formulas that depend heavily on row counts can break during restructuring. INDEX/MATCH, XLOOKUP, Tables, and named ranges are often more resilient.
Trace dependents before deleting
Before removing cells, use Formulas > Trace Dependents to see which formulas rely on them. It reveals the impact before the deletion.
Fix broken charts separately
Charts can break when source data is deleted. Right-click the chart, choose Select Data, and point the chart data range at valid cells.
Save a static backup before restructuring
Before major row, column, or sheet changes, copy important formula results and Paste Special > Values into a backup sheet or file.
Sources
- Microsoft Support – Correct a #REF! error (2025)
- Microsoft Support – Detect errors in formulas (2025)
- Microsoft Support – INDEX function (2025)