How to Fix an Excel #REF! Error After Deleting or Moving Cells
Question details
The user needs to resolve a #REF! error in a spreadsheet formula caused by deleting or moving referenced cells, specifically when the Undo action is no longer available.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Deleting or moving cells that are actively referenced by formulas, resulting in broken cell links.
- Observed behavior
- The affected formula stops calculating correctly and instead displays a #REF! error.
Before proceeding with manual fixes, check if you can simply use the 'Undo' shortcut (Ctrl + Z on Windows or Cmd + Z on Mac) to instantly restore the deleted cells and resolve the formula error.
Fill Down from a Correct Adjacent Formula
Use this method to quickly overwrite the broken formula if you have a column of identical, properly working formulas right above or below the error.
When dealing with repetitive calculations across a column or row, deleting a specific row can break the formula in just one or two cells. Using the fill handle is the fastest way to repair these isolated errors by copying the logic from an unbroken neighboring cell.
Click on the cell directly above or below the #REF! error that still contains a correct, working formula.
Hover your mouse cursor over the bottom-right corner of the selected cell until the cursor turns into a black cross (the fill handle).
Click and drag the fill handle over the cell or range of cells displaying the #REF! error (for example, down through D99:D112) to replace them with the correct formula.

Manually Replace the Broken Reference
Manually edit the formula to point to the correct cell if you know where the new data is located.
Restore Data from a Previous Version
If the deleted cell contained unique, important data that cannot be recreated by copying formulas, you will need to restore the data.
Resolve Formula Errors Easily Using WPS Spreadsheet
WPS Spreadsheet is a powerful, highly compatible tool that helps you manage formulas, trace error sources, and seamlessly handle Microsoft Excel files without the hassle.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing the #REF! error.
- 2. Locate the error: Click the cell displaying the error to view its contents in the top formula bar.
- 3. Edit the formula: Highlight the broken #REF! text, select the correct data range with your mouse, and press Enter to recalculate.

Frequently Asked Questions
What is the difference between a #REF! error and a #VALUE! error?
A #REF! error specifically indicates a missing or invalid cell reference, usually because a row, column, or sheet was deleted. A #VALUE! error means the wrong type of data is being used in the formula, such as trying to multiply a number by text.
How do I quickly find all #REF! errors in my worksheet?
You can press Ctrl+G (or F5) to open the Go To dialog. Click 'Special', select 'Formulas', uncheck all the boxes except 'Errors', and click OK. This will automatically select and highlight all cells containing errors in your sheet.
Will simply clearing a cell's contents cause a #REF! error?
No. Pressing Delete or Backspace to clear the contents of a cell leaves the cell intact but empty (which Excel treats as a 0 in calculations). A #REF! error only occurs when the cell itself is completely removed, such as by right-clicking and selecting 'Delete' on the entire row or column.
How can I prevent #REF! errors when organizing my data?
Instead of deleting entire cells, rows, or columns, clear their contents. If you need to move data, use the Cut and Paste commands rather than deleting and typing it again, as Excel automatically updates formula references when cells are cut and pasted.




