Formula Errors

How to Fix an Excel #REF! Error After Deleting or Moving Cells

Amos GikundaAmos Gikunda Nov 11, 2026 868 views

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.

How to Fix an Excel #REF! Error After Deleting or Moving Cells
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 you start

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.

Solution 1Recommended

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.

1
Select a valid cell

Click on the cell directly above or below the #REF! error that still contains a correct, working formula.

2
Use the fill handle

Hover your mouse cursor over the bottom-right corner of the selected cell until the cursor turns into a black cross (the fill handle).

3
Drag over the error

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.

Fill Down from a Correct Adjacent Formula
Formulas Restored: The copied formula will automatically adjust its relative references to match the new rows, fixing the #REF! error instantly.
Efficient Data Management

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. 1. Open your file: Launch WPS Spreadsheet and open the document containing the #REF! error.
  2. 2. Locate the error: Click the cell displaying the error to view its contents in the top formula bar.
  3. 3. Edit the formula: Highlight the broken #REF! text, select the correct data range with your mouse, and press Enter to recalculate.
Seamless compatibility with Microsoft Excel (.xlsx, .xls, .csv) formatsBuilt-in error checking and formula auditing tools to trace issuesLightweight and fast performance for handling large datasetsFree to use with a familiar, user-friendly interface
microsoft office alternative - wps office

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.