Manage Circular References in LibreOffice Calc

This guide explains how to manage circular references and configure iterative calculations in LibreOffice Calc. While circular references typically produce formula errors by default, enabling iterative calculations allows Calc to repeatedly recalculate formulas until a specific condition or limit is met. Below, you will learn why circular references occur, how to enable and configure iterative calculations for intentional recursive formulas, and how to find and resolve accidental formula loops.

What Causes a Circular Reference in LibreOffice Calc

A circular reference occurs when a formula refers directly or indirectly to its own cell. For example, entering =A1+1 into cell A1 creates an infinite loop because the formula needs the value of A1 to calculate A1.

By default, LibreOffice Calc halts calculation and displays the Err:522 error message in the affected cell to prevent infinite loops and freezing.

How to Enable and Configure Iterative Calculations

If your calculation model requires circular dependencies—such as financial modeling, interest calculations, or engineering approximations—you can enable iterative calculations.

1. Open LibreOffice Calc.
2. Go to the menu bar and select Tools > Options (on macOS, select LibreOffice > Preferences).
3. In the left pane, expand the LibreOffice Calc section and click on Calculate.
4. In the right pane, locate the Iterative references section and check the Iterations box.
5. Configure the iteration limits:
   • Steps: The maximum number of calculation cycles Calc will perform before stopping (e.g., 100).
   • Minimum Change: The threshold value that determines when calculations stop. If the difference between the current calculation result and the previous result is less than or equal to this value, Calc stops iterating before reaching the maximum steps (e.g., 0.001).
6. Click Apply, then OK.

Calc will now calculate the formula repeatedly until it reaches the set Minimum Change threshold or the maximum Steps limit.

How to Find and Fix Accidental Circular References

If a circular reference was created by mistake, keep iterations disabled and locate the formula error:

1. Look for cells displaying Err:522 or check the status bar at the bottom of the window for circular reference alerts.
2. Use the Detective tool to trace formula dependencies:
   • Select the problematic cell.
   • Go to Tools > Detective > Trace Precedents (or press Shift + F7).
   • Go to Tools > Detective > Trace Dependents (or press Shift + F5).
3. Follow the red and blue tracer arrows to identify which cells in the chain refer back to the starting cell.
4. Edit the formula to remove the self-referencing cell coordinate.
5. To clear the visual arrows, go to Tools > Detective > Remove All Traces.

Best Practices

• Keep Iterations Disabled by Default: Only enable iterations when a specific spreadsheet model requires them. Leaving iterations enabled globally can hide unintended formula errors in standard spreadsheets.
• Set Realistic Limits: Setting the maximum steps too high or the minimum change too low can cause spreadsheet lag during recalculations.
• Document Intentional Loops: When using intentional iterative calculations, add cell comments to inform other users that the circular references are deliberate.