Circular Reference in Excel: How to Find and Fix It

To fix a circular reference in Excel, open Formulas > Error Checking > Circular References, select a listed cell, and edit its formula so it no longer depends on itself, directly or through other cells. If the loop is deliberate, enable iterative calculation instead; do that only when the workbook is designed to calculate repeatedly.

What a circular reference means

A formula has a circular reference when its result depends on its own value. The dependency can be direct, such as a formula in A1 that refers to A1, or indirect, when two or more formulas lead back to the starting cell. Excel cannot calculate an ordinary formula chain in a simple, one-way order if that chain loops back on itself.

For a direct example, with a formula entered in A1, =A1+1 asks Excel to calculate A1 by first knowing A1. For an indirect example, with A2 and B2 used in the same worksheet, put =B2*1.1 in A2, then put =A2-5 in B2. A2 needs B2, and B2 needs A2, so the dependency forms a loop.

A circular reference is not automatically a sign that the arithmetic or the business idea is wrong. It often appears because a formula was entered in the total cell itself, a copied range shifted references unexpectedly, or a model intentionally uses repeated calculations. The right fix depends on whether the loop was accidental or planned.

Find the cell causing the warning

In Excel for Microsoft 365 on Windows, use Formulas > Error Checking > Circular References. If Excel detects a loop, the menu lists a cell address. Select an address to move to that cell, then inspect its formula in the formula bar. If there are multiple circular references, return to the menu after checking each address; correcting one loop may expose another or remove several connected loops at once.

Excel may also show a circular-reference notice when a formula is entered. The status bar can display a circular-reference message and cell address. These clues help locate a problem, but the address is only a starting point: an indirect loop may include several formulas, and the listed cell may not be the only one that needs correction.

If the menu is unavailable or the loop is hard to follow, search the workbook’s formulas and inspect the cells used by the reported formula. Check formulas on other worksheets too. A formula can refer to a different sheet, so the cycle may cross tabs even when the visible cell seems unrelated. In Excel for the web, use the desktop app for the full circular-reference controls and calculation settings if they are not available in the browser.

Trace the dependency chain

Start with the flagged cell and read its formula from left to right. Identify every referenced cell, then inspect those cells’ formulas in turn. Continue until a reference leads back to the original cell. This is the dependency chain that makes the reference circular. For a small workbook, following cell addresses manually is often quickest; for a larger one, write down the chain so you can see where the unintended link enters.

Excel’s formula-auditing tools can help. Select a formula cell and choose Formulas > Trace Precedents to show cells it depends on, or Formulas > Trace Dependents to show cells that depend on it. Arrows can point to other worksheets or workbooks, and Excel may use a worksheet icon or dotted arrow for references that cannot be shown as a normal on-sheet path. Select Remove Arrows when you have finished tracing.

Look especially for formulas that include their own output range. For instance, a SUM formula in the last row of a column can accidentally include its own cell if the selected range extends one row too far. Also check copied formulas for shifted references, named ranges, and formulas that refer to cells on a different worksheet. The loop may be created by any of these, not just by an obvious self-reference.

Fix an accidental circular reference

Most accidental loops are fixed by changing the formula, moving it to a different cell, or adjusting the range. Before editing, decide what the formula is intended to calculate and which cells should be inputs versus outputs. Do not simply delete a value or replace the formula with a constant until you understand what other calculations rely on it.

Move a total outside its input range. Put the total in a separate row or column, then make sure the SUM range includes only the underlying values.
Correct an inclusive range. If a total in B10 should add B2 through B9, keep B10 out of the range rather than including the formula cell.
Separate an input from a calculated result. A cell should not be both the rate or amount someone enters and the output that is calculated from that input.
Break an unintended dependency. If one formula should use a fixed assumption, reference a dedicated input cell rather than a result that eventually depends on the formula.

For example, with item prices in B2:B10 and the total in B11, enter =SUM(B2:B10) in B11. The total adds the nine item prices and does not include B11 itself. If the total were placed in B10 while the range still included B10, the formula would contain its own result and create a circular reference.

Use a layout that keeps inputs and outputs separate

Consider a small invoice worksheet with the item price in B2, the tax rate in C2, tax in D2, and the final total in E2. With those cells holding those values, enter =B2*C2 in D2. If B2 is 100 and C2 is 0.08, D2 returns 8. With the same layout, enter =B2+D2 in E2; it returns 108. Each formula uses inputs or an earlier result, but neither refers to its own output cell.

This structure is easier to check than putting a manually entered tax amount and the calculated total in the same cell. Keep assumptions in clearly labeled input cells, calculations in separate cells, and final results in a summary area. Labels and consistent formatting do not prevent every mistake, but they make it easier to notice when a formula refers to the wrong part of the model.

A frequent misconception is that any formula referencing its own row is circular. It is not: a formula can refer to other cells on the same row without looping. The problem occurs only when following its references eventually leads back to that formula’s cell. Conversely, a formula can be circular even if it does not visibly mention its own address, because another formula may complete the loop.

When iterative calculation is appropriate

Some financial and engineering models deliberately use circular calculations. For example, a spreadsheet might calculate interest from a balance that itself includes accrued interest, or calculate a fee based on a profit figure that is reduced by that fee. In such a model, the formula is intended to be recalculated repeatedly until the result settles near a stable value. Removing the loop may change the intended model, so first confirm that the circularity is part of the design.

To allow iteration in Excel for Microsoft 365 on Windows, choose File > Options > Formulas, select Enable iterative calculation, then set Maximum Iterations and Maximum Change as appropriate. Maximum Iterations limits how many calculation passes Excel makes; Maximum Change sets the threshold for deciding that successive results are close enough. A smaller change threshold can demand greater precision but may require more calculations.

Iteration is not a general repair for a broken formula. Results can depend on the starting values and calculation settings, and a loop may fail to settle or may produce a result that is not meaningful for the model. Use documented assumptions, test sensible input ranges, and compare results with an independent calculation where possible. To turn iteration off, return to the same setting and clear Enable iterative calculation. On Mac, use Excel’s calculation settings; menu labels and locations can vary by release. Excel for the web may require opening the workbook in the desktop app to change this setting.

Common problems after editing

If Excel still reports a circular reference after you change a formula, check the Circular References menu again and inspect every listed address. A second, separate loop may remain. Also review formulas on hidden worksheets and any formulas that use named ranges: a name can point to a cell that is not obvious from the formula text.

Be cautious when copying and pasting formulas. Relative references move when copied, which can make a formula point back to its own cell in a new location. If a reference should stay fixed as formulas are copied, use an absolute reference with dollar signs, such as =$C$2*B2. With the rate in C2 and an amount in B2, this formula multiplies the amount by the fixed rate cell while allowing the B2 reference to shift when copied down. Absolute references prevent unwanted movement but do not, by themselves, fix an existing circular dependency.

If the warning appears only in one workbook, check whether iterative calculation is enabled or whether the file contains deliberate loops. If it appears after a recent edit, undoing that edit may help identify the change, but do not rely on undo as the repair: correct the formula structure and verify the workbook’s results afterward. Save a copy before substantial changes to a complex model.

Prevent circular references in future work

Use a predictable worksheet structure: inputs in one area, intermediate calculations in another, and totals or reports in a separate area. Keep sum ranges visibly distinct from their result cells, and review the first and last cells of a range after inserting rows or copying formulas. When a workbook is shared, label assumptions and explain any intentional iterative calculations so another person does not mistake them for errors.

Before relying on a workbook, check the circular-reference menu after major formula changes and test a few known inputs whose correct results can be calculated independently. A workbook that shows no warning is not necessarily correct—ordinary formula mistakes can exist without a circular reference—but finding and resolving loops removes one important source of unpredictable results.

Frequently Asked Questions

Why does Excel say there is a circular reference?
At least one formula depends on its own result, either directly or through a chain of other formulas. Excel identifies the loop because it cannot calculate that chain as an ordinary one-way sequence.

Where do I find circular references in Excel?
In Excel for Microsoft 365 on Windows, choose Formulas > Error Checking > Circular References. Select a listed cell address to go to it, then trace the formula’s references to find the full loop.

Can I ignore a circular reference warning?
Only if the loop is intentional and the workbook is designed to use iterative calculation. Otherwise, ignoring it can leave a result dependent on calculation settings or prevent it from updating as expected.

How do I stop Excel from showing a circular reference?
Break the formula dependency by changing the formula, moving the result cell, or correcting the referenced range. If the loop is deliberately required, enable iterative calculation in the desktop app and choose suitable iteration settings.

Does a circular reference always make the result wrong?
Not necessarily. An intentional model can use iterative calculation to reach a useful result, but an accidental loop can produce an incomplete or misleading calculation. Confirm the model’s design and validate the result rather than assuming every warning is harmless.

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.