Fix Excel Formulas That Do Not Update After Rows Are Inserted
Question details
Identify why certain Excel formulas retain old row references or return #REF! after rows are inserted or deleted on another worksheet.

- 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.
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.
Inspect the affected formulas and their references
Use this approach first to distinguish formulas that contain text-based references from standard Excel references.
Select an affected cell and examine the formula bar. Look for INDIRECT, references written as text, external workbook links, or defined names.
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.
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.
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.
If the workbook uses macros, open Developer > Visual Basic and check code that runs during worksheet changes or rewrites formulas and references.

Test Excel in Safe Mode and inspect startup files
On Windows, use Safe Mode to check whether an add-in or startup file is contributing to the problem.
Update or repair Microsoft Office
Try an Office update or repair if standard references still behave unexpectedly after formula and Safe Mode checks.
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.

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!.




