Fix Circular References and Formula Calculation in Excel 2016
Question details
The user needs Excel 2016 to update formulas and cross-sheet references reliably without manual intervention.

- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- A workbook uses formulas and references to values on other sheets.
- Observed behavior
- Excel intermittently reports circular references, stops updating formulas, and does not refresh cross-sheet values until cells are edited or recalculated manually.
Open the affected workbook and identify a formula and a cross-sheet reference that are not updating. Check whether the workbook intentionally uses iterative calculations before changing any circular-reference settings.
Enable automatic calculation and check circular references
Use this first when formulas or cross-sheet values update only after manual recalculation.
In Excel, select Formulas > Calculation Options > Automatic.
Select Formulas > Error Checking > Circular References, then select a listed cell to inspect its formula. Check whether it refers to itself directly or through other cells.
Edit any unintended circular formulas and verify that cross-sheet formulas point to the intended sheet and cells.
Select Formulas > Calculate Now, then change a source value and confirm that the dependent formula updates automatically.

Repair Microsoft Office if calculation remains inconsistent
Use Office repair if automatic calculation is enabled and the workbook's formulas are correct, but Excel still behaves inconsistently.
Work with spreadsheet formulas in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files in WPS Spreadsheets for an easy transition, while checking important formulas and workbook features after migration.

Frequently Asked Questions
Where is automatic calculation in Excel 2016?
Open the Formulas tab, select Calculation Options, and choose Automatic.
How can I find the cell causing a circular reference?
Select Formulas > Error Checking > Circular References and choose a listed cell. Inspect its formula and any cells it references to find the loop.
Why might a cross-sheet formula not update?
Calculation may be set to Manual, or the formula may reference the wrong cell or sheet. Check Formulas > Calculation Options and inspect the formula's sheet and cell references.
Should I remove every circular reference?
No. Some workbooks use circular references intentionally with iterative calculation. Check File > Options > Formulas > Enable iterative calculation and confirm the workbook's intended design before changing it.
What should I do if Office repair does not fix the workbook?
Test another workbook in Excel and inspect the affected file's formulas and references. If the issue is limited to one workbook, its formulas or settings may need further investigation.




