Circular Cell Reference in Excel: Causes and Fixes

How-to basics · Updated November 18, 2026

A circular cell reference in Excel occurs when a formula refers to its own cell, either directly or through a chain of other formulas. Find the loop using Formulas > Error Checking > Circular References, then change a formula to break it—or enable iterative calculation only when the circular logic is intentional.

What a circular reference means

Excel formulas normally depend on cells that contain inputs or earlier calculations. For example, a total in one cell can add values from several other cells. A circular reference appears when Excel cannot follow those dependencies in one direction because a calculation eventually leads back to itself.

A direct circular reference is easy to spot: a formula in a cell includes that same cell in its calculation. An indirect circular reference involves two or more cells. For instance, a formula in A1 may depend on B1, while a formula in B1 depends on A1. The loop can also cross worksheets, so a formula on one sheet may lead through other sheets and return to its starting cell.

A circular reference is not simply a formula that uses a value calculated elsewhere. It is a cycle in the dependency chain. Excel can sometimes calculate a circular model through repeated iterations, but it cannot produce an ordinary one-pass result unless the cycle is removed or iteration is enabled.

How Excel detects the loop

Excel tracks the cells that formulas depend on so it can recalculate results when inputs change. When a dependency leads back to the formula being calculated, Excel identifies a circular reference. Depending on the workbook and how the reference was created, Excel may display a warning, identify a cell in the status bar, or list a location under the Circular References menu.

Without iterative calculation, Excel generally cannot resolve the loop as a normal formula. A cell may display zero or a previous value in some circumstances, but that is not evidence that the formula is correct. Treat the circular-reference warning as a prompt to inspect the logic, not as a harmless notification to dismiss.

Excel can report one reference at a time even when a workbook contains several loops. After you correct the reported reference, check the menu again. A second circular reference may become visible only after the first one is fixed.

Common causes and a simple example

Accidental circular references often arise from a range that includes the cell containing its own total. Suppose B2 is intended to add the values in B3 through B10, but the selected range begins at B2 instead. With the total in B2 and inputs in B3:B10, the formula =SUM(B2:B10) includes its own result. Excel flags a circular reference rather than returning a dependable total.

The correction is to use the input range only. With values 5, 6, and 4 in B3:B5 and the remaining cells in B6:B10 blank, enter =SUM(B3:B10) in B2; it returns 15. Always check that a subtotal or grand total does not fall inside the range it is adding.

Other frequent sources include:

  • A copied formula whose relative references shifted back to its own cell.
  • A balance, inventory, or running-total formula that includes the cell where its result is stored.
  • Two or more formulas that refer to one another, sometimes on different worksheets.
  • A formula edited to point to a summary cell that depends on the original formula.
  • A named range or indirect reference that hides the dependency from a quick visual check.

Some loops are deliberate—for example, a model in which interest depends on a balance that itself includes interest. In that case, the circular dependency is part of the model, not automatically a mistake. The key is to know what relationship is being represented and how Excel will calculate it.

How to find a circular reference

In Excel for Microsoft 365 on Windows, open the Formulas tab and choose Error Checking > Circular References. If Excel has detected a loop, the menu lists a cell address; select it to go to that cell. If more than one loop exists, fix the listed one and repeat the check. The address identifies a useful starting point, but it may not show every formula involved in an indirect cycle.

Inspect the formula bar and follow the referenced cells. For a complicated workbook, use Formulas > Trace Precedents to see which cells feed the selected formula, and Formulas > Trace Dependents to see which formulas rely on it. Follow the arrows through the chain until a reference returns to the starting cell. These tracing tools can be used repeatedly across worksheets, though you may need to select cells on each sheet to understand the full path.

You can also use Find to look for a cell address in formulas, but this is less reliable when formulas use names, table references, or indirect references. If you cannot locate the loop, check formulas recently copied or edited, named ranges, and cells used by summaries. On Mac, the relevant commands are also on the Formulas tab, though menu labels and placement can vary slightly by Excel release.

Ways to fix an accidental reference

First decide which cells are meant to be inputs and which are meant to be results. Then adjust the formula so the result cell is not included in its own inputs. A common repair is shortening a sum range, as in the B2 example above. If a copied formula shifted unexpectedly, use the correct cell references or lock a fixed reference with dollar signs where appropriate.

For an indirect loop, map the chain before editing. If A1 depends on B1 and B1 depends on C1, determine why C1 eventually depends on A1. Often one formula points to a summary or output cell when it should point to a separate input cell. A clear separation between input cells, intermediate calculations, and final outputs makes this easier to diagnose.

Do not enable iterative calculation just to silence an unexplained warning. That can make a workbook appear to work while leaving an incorrect dependency in place. After changing formulas, confirm that outputs make sense for known inputs, check the Circular References menu again, and test relevant totals or balances.

When iterative calculation is appropriate

Some models intentionally contain circular logic. A simplified example is an amount of interest calculated on an opening balance plus the interest itself. With an opening balance of 100 in A1, a rate of 10% in A2, and the interest result in B2, the formula =(A1+B2)*A2 in B2 refers to itself. If iterative calculation is enabled, Excel repeatedly recalculates it. The values approach about 11.1111, the fixed-point solution to the relationship.

This is a mathematical illustration, not a recommendation to model a real loan this way. The correct treatment of interest depends on the actual terms and timing. In this example, an equivalent non-circular calculation can be placed in C2: with the same A1 and A2 inputs, =A1*A2/(1-A2) returns approximately 11.1111 without a circular reference. That algebraic alternative is preferable when the relationship can be rewritten clearly and safely.

Iteration may be useful when a model genuinely requires a circular relationship and no suitable non-circular formulation is available. It is important to understand that Excel is approximating a result by recalculating repeatedly; it is not proving that every circular model has a unique or sensible answer. Some relationships may fail to converge, oscillate, or depend strongly on the starting values.

Enable or adjust iterative calculation

On Windows in Excel for Microsoft 365, go to File > Options > Formulas. Under Calculation options, select Enable iterative calculation. You can then set Maximum Iterations, which limits how many recalculations Excel performs, and Maximum Change, which sets how small the change between results must become before Excel stops. Excel’s default values are 100 iterations and 0.001 maximum change.

Increasing the iteration limit or reducing the maximum change may make a converging calculation more precise, but it cannot repair a flawed model or guarantee convergence. Use settings that fit the calculation and check the resulting values. Iterative calculation is a workbook setting, so review it when opening or sharing a workbook that depends on circular formulas.

On Mac, look in Excel > Settings (called Preferences in some releases) > Calculation for the iterative calculation options. The exact dialog wording can vary by version. If you do not see the option, check that you are using the desktop Excel application and consult the settings available in your installed release.

Common problems and prevention

A reference can remain after an apparent fix because another formula still completes the cycle. Recheck Error Checking > Circular References after each correction. Also distinguish a circular-reference warning from other formula errors: #VALUE! or #REF! do not by themselves mean that a formula is circular.

To reduce accidental loops, keep inputs separate from calculated totals, avoid including total cells inside the ranges they summarize, and review formulas after inserting rows or copying them across a sheet. For larger models, document intentional circular calculations and record the iteration settings the workbook requires. If a workbook is used by other people, explain which circular formulas are deliberate so they are not mistaken for errors.

Frequently Asked Questions

How do I find circular references in Excel?
On Windows, choose Formulas > Error Checking > Circular References. Select a listed cell, inspect its formula, and follow its dependencies. Repeat the check after making a correction because another loop may then be reported.

Why does Excel say there is a circular reference when I cannot see one?
The loop may involve several cells, another worksheet, a named range, or a formula copied with shifted references. Start at the cell Excel lists and trace its precedents and dependents until the chain returns to that cell.

Can a circular reference be intentional?
Yes. Some models use a relationship in which one calculated value feeds back into another. Enable iterative calculation only when that dependency is deliberate, understood, and checked for convergence; otherwise, rewrite the formulas to remove the loop.

Will enabling iterative calculation fix every circular reference?
No. It lets Excel recalculate a circular formula repeatedly, but a model may not converge or may produce an unsuitable result. It also does not correct an accidental reference such as a total cell included in its own sum range.

Why does a circular-reference cell show zero?
Zero can appear when Excel cannot calculate a circular formula normally, but it does not establish that zero is the intended result. Inspect the dependency chain and either break the loop or enable iteration for a valid, deliberate circular model.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.