Excel Absolute Reference Changes During Recalculation
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.
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.
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.
Press F9 once and wait for calculation to finish before pressing it again. Watch Excel's status bar for calculation progress.
Select the cell containing =Assumptions!$I$11 after calculation finishes and compare its displayed value with Assumptions!I11.
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.

Test the workbook and Excel add-ins separately
Use a new workbook and Safe Mode to narrow down whether the behavior is related to the file or the Excel environment.
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.

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.




