Calculation Issues

Excel Absolute Reference Changes During Recalculation

Jason MillerJason Miller Nov 14, 2026 869 views

Question details

Determine why a cell with an absolute reference temporarily displays changing values during repeated recalculation.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A financial model uses circular references and iterative calculation. The user presses F9 repeatedly to recalculate the workbook.
Observed behavior
A cell containing =Assumptions!$I$11 temporarily displays changing values even though the referenced cell contains the hard-coded value 8. The behavior occurs on multiple computers but was not observed in Excel 2007.
Before you start

Check that Assumptions!I11 still contains the hard-coded value 8, and note the workbook's iterative calculation settings. Confirm which Excel version you are using so you can compare results across versions.

Solution 1Recommended

Let recalculation finish and review iteration settings

Use this approach first to distinguish temporary calculation results from a persistent incorrect value.

An absolute reference fixes the referenced address when a formula is copied; it does not freeze the value displayed during calculation. Circular models can show intermediate values while Excel works through iterations.

1
Recalculate once

Press F9 once and wait for calculation to finish before pressing it again. Watch Excel's status bar for calculation progress.

2
Check the final value

Select the cell containing =Assumptions!$I$11 after calculation finishes and compare its displayed value with Assumptions!I11.

3
Review iteration options

Open File > Options > Formulas and review Enable iterative calculation, Maximum Iterations, and Maximum Change. Adjust them only as appropriate for the financial model, then recalculate again.

Let recalculation finish and review iteration settings
If the final value remains incorrect: A formula directly referencing a hard-coded 8 should normally display 8 after calculation completes. If it does not, continue with the isolation tests.
Free Microsoft Office alternative

Work with Financial Models in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for moving existing files. It does not repair Excel's recalculation behavior; check circular-model results and calculation settings when migrating.

Open and edit Microsoft Excel XLSX and XLS filesReview financial model formulas and cell references in WPS SpreadsheetUse a familiar interface for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does an absolute reference lock a cell's value?

No. In =Assumptions!$I$11, the dollar signs lock the column and row reference when the formula is copied. The displayed result still depends on the referenced cell and calculation state.

Why should I wait between F9 presses?

Repeated F9 presses can request recalculation while Excel is still working through an iterative model. Press F9 once and wait for the calculation progress on the status bar to finish before checking the result.

Where can I find Excel's iterative calculation settings?

Open File > Options > Formulas. Under calculation options, check whether iterative calculation is enabled and review Maximum Iterations and Maximum Change.

How can I tell whether an add-in is involved?

On Windows, close Excel and run excel /safe from Windows+R. Open the workbook and test again. If the behavior changes, use File > Options > Add-ins to investigate add-ins in a normal session.