Formula Errors

Fix Excel Formulas That Do Not Update After Rows Are Inserted

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 868 views

Question details

Identify why certain Excel formulas retain old row references or return #REF! after rows are inserted or deleted on another worksheet.

Fix Excel Formulas That Do Not Update After Rows Are Inserted
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user inserts or deletes rows on one worksheet and expects formulas on another worksheet to reflect the changes.
Observed behavior
Some formulas continue to reference an old row, while others return #REF! errors; only certain formulas in the workbook are affected.
Before you start

Identify an affected formula and the row operation that triggers the problem. Check whether the row was inserted or deleted, because deleting a referenced cell can legitimately produce a #REF! error.

Solution 1Recommended

Inspect the affected formulas and their references

Use this approach first to distinguish formulas that contain text-based references from standard Excel references.

1
Inspect an affected formula

Select an affected cell and examine the formula bar. Look for INDIRECT, references written as text, external workbook links, or defined names.

2
Check names and table references

Open Formulas > Name Manager to inspect defined names used by the formula. If it uses a table reference, check the table and column names on the source worksheet.

3
Replace text-based references where appropriate

Edit the formula bar to use a direct reference, such as =Sheet2!D42, instead of an INDIRECT expression containing a fixed address, if the formula does not need a text-based reference.

4
Check deleted references and calculation

If a formula shows #REF!, edit it to point to the intended existing cell or range. Also check Formulas > Calculation Options > Automatic; recalculation does not restore a deleted reference.

5
Review workbook macros

If the workbook uses macros, open Developer > Visual Basic and check code that runs during worksheet changes or rewrites formulas and references.

Inspect the affected formulas and their references
Absolute references can still adjust: A reference such as $D$42 is absolute when copied, but Excel can still adjust it when rows are inserted. INDIRECT and other references stored as text may not adjust in the same way.
Free Microsoft Office alternative

Work with Spreadsheet Formulas in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks in WPS Spreadsheet to review formulas and make a seamless transition between office suites; it does not repair the underlying Microsoft Excel installation.

Review and edit spreadsheet formulas in WPS Spreadsheet.Open and save Microsoft Excel XLSX and XLS files.Use a familiar ribbon-style interface for everyday spreadsheet work.Get a free, lightweight office suite for documents, spreadsheets, and presentations.
microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT keep referring to the old row?

INDIRECT constructs a reference from text, and Excel may not adjust that text when rows are inserted. Select the formula cell and check the formula bar; where suitable, replace the text-based reference with a direct cell reference.

Do absolute references update when I insert rows?

They can. The dollar signs in a reference such as $D$42 control how it changes when copied, but Excel can still adjust the reference when rows are inserted.

Why did my formula change to #REF! after deleting a row?

Deleting a cell or row that a formula directly depends on can invalidate the reference. Select the formula cell and use the formula bar to replace #REF! with the intended existing cell or range.

How can I tell if an Excel add-in is involved?

On Windows, close Excel, press Windows+R, and run excel /safe. If the issue disappears, reopen Excel normally and use File > Options > Add-ins to disable add-ins selectively and retest.

Will setting calculation to Automatic fix the references?

Automatic calculation can refresh formula results, but it does not necessarily repair incorrect or deleted references. Check Formulas > Calculation Options > Automatic, then inspect any formulas that still point to the wrong cells or show #REF!.