Circular Reference Not Working in Excel? Fix It

A circular reference in Excel will not calculate as expected if iterative calculation is off, the formula does not converge, or a reference creates a loop by mistake. Find the cell involved under Formulas > Error Checking > Circular References; if the loop is intentional, enable iteration in File > Options > Formulas and check Enable iterative calculation.

What a circular reference means

A circular reference occurs when a formula depends on its own result, either directly or through other cells. For example, a formula in B2 that refers to B2 is a direct circular reference. A less obvious loop can run through several cells: A1 refers to B1, B1 refers to C1, and C1 refers back to A1. Excel cannot calculate that chain as a one-way sequence because it has no starting value that is independent of the loop.

Most circular references are accidental. A formula copied into the wrong row, a range that includes its own total cell, or a mistaken cell address can create one. But circular references can also model a relationship that is solved through repeated approximations. In that case, Excel needs iterative calculation enabled, and the loop needs to settle toward a stable answer.

Excel normally warns when a circular reference is entered. Depending on the workbook and how the formula was introduced, the result may appear as zero, a previous value, or a value that does not update as you expect. A warning alone does not tell you whether the loop is intentional, so inspect the references before changing calculation settings.

Find the cell causing the problem

In Excel for Microsoft 365 on Windows, open Formulas > Error Checking > Circular References. If Excel identifies a circular reference, the menu lists a cell address; select it to jump to that cell. When there are multiple loops, Excel may show one reference at a time. Correcting one can reveal another, so repeat the check until the menu no longer lists a circular reference.

Trace the formula chain rather than looking only at the flagged cell. Select a formula cell and use Formulas > Trace Precedents to see which cells feed into it, or Formulas > Trace Dependents to see which cells use its result. These arrows can help expose an indirect loop. Remove arrows with Formulas > Remove Arrows when you are finished. You can also inspect the formula bar and click each referenced cell to follow the chain.

Check totals and subtotals: a SUM range that includes the cell containing the SUM can make the result circular.
Check copied formulas: a relative reference may have shifted into the formula’s own row or column.
Check formulas across sheets: a loop can pass from one worksheet to another, even if each individual formula looks reasonable.
Check named ranges and tables: a reference may point to a range broader than its label suggests.

For instance, if a total is in B10, make sure the range being added ends at B9 rather than including B10. This is a common accidental loop. If the flagged formula is supposed to use its own prior result, do not simply delete the reference; first confirm the intended calculation and set up iteration deliberately.

Enable iterative calculation for an intentional loop

On Windows, choose File > Options > Formulas. Under Calculation options, check Enable iterative calculation, then set Maximum Iterations and Maximum Change if their defaults are not suitable. The settings are saved with the workbook. Reopen the file and confirm the workbook still calculates as intended, especially if it will be shared with other people.

On a Mac, open Excel > Settings (called Preferences in some releases) > Calculation, then enable iterative calculation and adjust the iteration limits. The labels or layout can vary slightly between Excel releases. Excel for the web does not provide the same desktop calculation-options dialog, so use the desktop app to configure the workbook’s iteration settings.

Maximum Iterations is the number of calculation passes Excel can make before stopping. Maximum Change is the tolerance: iteration stops when the change between passes becomes smaller than that value, or when the iteration limit is reached. A very small tolerance can require more passes. A large tolerance may stop sooner but leave a less precise result. The right values depend on the formula and the precision the workbook needs; do not raise the iteration limit blindly to hide an unstable calculation.

Test a circular formula with a simple example

Suppose A2 contains 1000, C1 contains 5%, and B2 is intended to equal the starting amount plus 5% of the result in B2. With that layout, enter this formula in B2:

=A2+B2*$C$1

This deliberately refers to B2 itself. With iterative calculation enabled, repeated passes approach 1,052.63: the equation is equivalent to B2 = 1000 + 0.05 × B2, whose stable solution is 1000 divided by 0.95. The result is an illustrative fixed-point calculation, not a recommended way to model a real loan, deposit, or investment. For actual money calculations, check the applicable terms with the provider or a qualified professional.

The example also shows why a circular formula can appear not to work even when iteration is enabled. If the rate were 100% or higher, repeated updates would not converge to a finite stable value in this setup. A negative rate or a more complicated relationship can also behave unexpectedly. Iteration is a calculation method, not a guarantee that every circular equation has a useful answer.

Why iteration may still give the wrong result

First, verify that you enabled iteration in the workbook that contains the formula. An Excel setting changed in a different open workbook may not be the setting you intended to use. Confirm the current workbook’s Calculation options and save it after making changes.

Next, check whether the formula is converging. A convergent loop produces results that get closer to a stable value with each pass. A loop can diverge, oscillate between values, or depend heavily on its starting value. For example, a formula that repeatedly adds a fixed positive amount to its own previous result keeps growing rather than settling. Increasing Maximum Iterations will not fix that underlying behavior.

Also check Maximum Change. If the tolerance is too large for the precision you need, Excel may stop while the result is still noticeably approximate. If it is extremely small, Excel may use many iterations or reach the iteration limit first. For a workbook used in reporting or decisions, document the chosen tolerance and compare the result against an independent calculation where possible.

Finally, make sure automatic calculation is enabled. On Windows, check Formulas > Calculation Options > Automatic. If calculation is set to Manual, formulas may remain unchanged until recalculation. Press F9 to recalculate open workbooks on Windows; on Mac, use Fn+F9 on keyboards where the function keys control system features, or use Formulas > Calculate Now. Recalculation alone does not resolve an accidental loop or enable iteration.

Remove an accidental circular reference instead

If the formula is not supposed to refer back to itself, fix the reference rather than enabling iterative calculation. For example, a sum cell should generally add the input cells above it, not include its own cell. In a copied formula, compare the intended row and column with the references Excel actually inserted. Use an absolute reference, such as $C$1, when a fixed input must remain fixed as a formula is copied.

For more complex workbooks, simplify the calculation into separate stages. Put inputs in one area, intermediate calculations in another, and final outputs in a separate cell or column. This makes dependencies easier to follow and reduces the chance of an accidental loop. If a circular relationship is genuinely part of the model, keep it clearly labeled and explain the iteration settings so another person does not mistake it for a broken formula.

Check version and workbook behavior

The Windows instructions above apply to Excel for Microsoft 365. Mac users should use Excel’s Calculation settings rather than the Windows File > Options path. The desktop settings are the reliable place to enable or adjust iterative calculation; if a workbook is opened in Excel for the web, the web interface may not offer the same controls. When a file is shared, ask the recipient to open it in a compatible desktop Excel version and verify the calculated result.

Other spreadsheet programs can handle circular references differently, so a workbook moved between Excel and another application may not behave identically. Even within Excel, a workbook that relies on iteration can produce unexpected results if someone changes its formulas, iteration limits, or calculation mode. Keep a copy of the original file before adjusting a complicated model, and test changes on a small, understandable example first.

Frequently Asked Questions

How do I find all circular references in Excel?
Use Formulas > Error Checking > Circular References to jump to listed cells. Fix or investigate each one, then check the menu again. Excel may reveal another loop after the first has been corrected.

Why does Excel show a circular reference warning?
A formula depends on its own result, directly or through other cells. This is often caused by a mistaken range or cell reference, but it can be intentional when the calculation is designed to iterate.

Can I calculate a circular reference without enabling iteration?
Excel will not repeatedly solve the loop unless iterative calculation is enabled. If the loop was accidental, edit the formula to remove the self-reference. If it is intentional, enable iteration and check that the calculation converges.

Why is my circular-reference result zero or blank?
Iteration may be disabled, the formula may be using an initial value that leads to an unexpected result, or calculation may be set to Manual. Check the formula chain, workbook calculation settings, and whether the loop can converge before relying on the displayed value.

Is a circular reference always an error?
No. It is an error when it is unintended or produces an unreliable result. Some models deliberately use a circular relationship and iterative calculation, but the formula, convergence behavior, and calculation limits should be understood and documented.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.