Calculation Issues

Fix Circular References and Formula Calculation in Excel 2016

Steven ParkerSteven Parker Nov 22, 2026 868 views

Question details

The user needs Excel 2016 to update formulas and cross-sheet references reliably without manual intervention.

Fix Circular References and Formula Calculation in Excel 2016
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.
Before you start

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.

Solution 1Recommended

Enable automatic calculation and check circular references

Use this first when formulas or cross-sheet values update only after manual recalculation.

1
Enable automatic calculation

In Excel, select Formulas > Calculation Options > Automatic.

2
Find circular references

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.

3
Correct the formulas

Edit any unintended circular formulas and verify that cross-sheet formulas point to the intended sheet and cells.

4
Recalculate and test

Select Formulas > Calculate Now, then change a source value and confirm that the dependent formula updates automatically.

Enable automatic calculation and check circular references
Intentional circular references: If the workbook deliberately uses circular formulas, review File > Options > Formulas > Enable iterative calculation and its iteration settings instead of removing those formulas.
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 files in WPS Spreadsheets for an easy transition, while checking important formulas and workbook features after migration.

Use spreadsheet formulas and cross-sheet references in WPS SpreadsheetsOpen and save compatible Microsoft Excel XLSX and XLS filesWork in a familiar, easy-to-use spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

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.