Circular Reference Error in Excel: Find and Fix It

A circular reference error in Excel means a formula depends on its own result, either directly or through other cells. To find one in Microsoft 365 for Windows, select Formulas > Error Checking > Circular References, then click a listed cell; correct the formula or deliberately enable iterative calculation if the loop is intentional.

What a circular reference means

Excel normally calculates a cell by following the formulas and values it depends on. A circular reference occurs when that chain eventually leads back to the cell where it began. The loop can be obvious, such as a formula in A1 that refers to A1, or indirect: A1 refers to B1, and B1 refers back to A1. A longer chain can involve many cells or even different worksheets.

For example, with a value in B1 and a formula entered in A1, the formula =A1+B1 asks Excel to calculate A1 using A1’s own not-yet-resolved result. Excel cannot calculate an ordinary, one-pass answer to that request, so it reports a circular reference. A circular reference is different from an error such as #VALUE! or #REF!: it describes a dependency loop, not necessarily a problem with the formula’s spelling or the referenced cell’s data type.

Some circular references are accidental, but they can also be part of a model designed to calculate repeatedly until the result settles. Excel’s default behavior is to flag a loop rather than keep recalculating it indefinitely. Whether a circular reference is a problem therefore depends on whether the loop is intended and whether the workbook’s calculation settings are appropriate.

Find the cell Excel has identified

On Excel for Microsoft 365 for Windows, use the circular-reference list on the Formulas tab. If the workbook contains a detected loop, Excel lists one or more cell addresses in the Error Checking menu. Selecting an address takes you to that cell. If more than one circular reference exists, follow the list and inspect each relevant location; fixing one loop may reveal or leave another.

Open the workbook and select the Formulas tab.
Select Error Checking, then point to or open Circular References.
Select a cell address in the list to jump to it.
Inspect the formula bar and the cells referenced by that formula. Follow the dependency chain if the formula does not refer to itself directly.

Excel may also show a circular-reference message when a formula is entered, and the status bar can identify a circular-reference condition and a cell address. These clues are useful, but the menu list is a practical starting point for navigation. If the list is empty after a change, recalculate or reopen the relevant menu and check whether another loop remains.

In Excel for Mac, the tab names are similar, but the exact menu presentation can vary by release. Look on the Formulas tab for Error Checking and Circular References. Excel for the web has a different interface and may not expose the same desktop auditing and calculation controls; if you cannot locate or configure the loop there, open the workbook in the Excel desktop app.

Fix the common accidental causes

A frequent cause is including a formula cell inside the range that the formula totals. Suppose B2 through B5 contain four amounts and B6 is meant to show their total. With amounts in B2:B5 and the total formula in B6, the correct formula is =SUM(B2:B5). It adds the four input cells and returns their total. If the formula in B6 instead uses =SUM(B2:B6), the range includes B6 itself, creating a direct circular reference.

Other accidental loops often come from entering a formula in the wrong cell, copying a formula into a row or column that it includes in its own range, or using a cell both as an input and as the output of a calculation. A circular reference can also be indirect: a summary cell depends on a detail cell, while that detail cell depends on the summary. Review the actual formula and the intended input/output layout rather than merely deleting a cell reference that looks suspicious.

Check range endpoints: Confirm that a total, average, or count range excludes the cell containing that formula unless the design genuinely requires iteration.
Check copied formulas: Look for a relative reference that shifted into the formula’s own row or column when the formula was filled down or across.
Separate inputs and outputs: Keep manually entered assumptions distinct from calculated results where practical.
Check worksheet references: Follow formulas across sheets if a cell’s direct formula does not appear to refer back to itself.
Inspect indirect references: Functions such as INDIRECT can make a dependency harder to see because the referenced address is assembled from text.

After editing a formula, verify both the displayed value and the formula’s intended range. A cell showing a number does not by itself prove the model is correct: if iterative calculation is already on, an accidental loop may produce a value instead of a prominent warning.

Trace a longer dependency loop

For a loop that is not obvious, start at the cell in the Circular References list and inspect the formula bar. Write down or follow the cell addresses it uses, then check each referenced cell in turn until you return to the starting cell. For example, if D4 depends on F4, F4 depends on B9, and B9 depends on D4, the loop is D4 → F4 → B9 → D4 even though none of the formulas needs to refer to its own cell directly.

Excel’s formula-auditing tools can help visualize part of this path. On Windows, select a formula cell and use Formulas > Trace Precedents to see cells feeding into it; Trace Dependents shows cells that use its result. Follow the arrows one cell at a time, switching worksheets if needed. These tools can be useful for ordinary cell references, but they may not display every dependency clearly, especially when formulas use indirect references, external workbooks, or complex worksheet structures. Treat the arrows as aids, not as a guarantee that every part of a loop is visible.

For a large workbook, temporarily add notes or a simple dependency map while investigating. Record the starting cell, the formulas along the chain, and the point where the chain returns to its start. This helps distinguish a genuine cycle from a formula that merely looks complicated. Avoid changing several formulas at once: make a targeted correction, then recheck the circular-reference list and the resulting values.

When iterative calculation is appropriate

Some business or engineering models intentionally rely on repeated calculation: a value is calculated using another value that itself depends on the first. Excel can process such a model through iterative calculation. Rather than treating the loop as a one-pass formula, it recalculates the cells repeatedly until it reaches the configured stopping limit or the changes become sufficiently small.

In Excel for Microsoft 365 on Windows, select File > Options > Formulas, then select Enable iterative calculation. The settings include Maximum Iterations, which limits the number of calculation passes, and Maximum Change, which sets a change threshold used to decide when to stop. Excel commonly starts with a maximum of 100 iterations and a maximum change of 0.001, but users or workbooks may have different settings. The right values depend on the model; a larger iteration limit is not automatically more accurate or safer.

For example, imagine two designated calculation cells where A1 is defined as half of 10 plus B1, and B1 is defined as half of A1. With A1 and B1 reserved for this linked calculation, enter =(10+B1)/2 in A1 and =A1*0.5 in B1. These equations converge toward A1 = 6.6667 and B1 = 3.3333, subject to Excel’s iteration and change settings. This is a small mathematical illustration, not a recommended financial model. The result depends on the starting values, formulas, and calculation settings, so a workbook should be checked against the intended logic.

Enabling iteration is a workbook calculation choice, not a repair for every circular-reference warning. It can allow unrelated accidental loops to calculate too, and a model may return a value that appears plausible while remaining wrong. Before enabling it, establish why the cycle is necessary, check the formulas and expected behavior, and document the chosen limits for other workbook users. If the loop is not intentional, remove the dependency instead.

On Mac, calculation preferences are available through Excel > Preferences > Calculation in many Microsoft 365 releases; the wording or location can differ across versions. If you are working in a different desktop release, use Excel’s help for that version to locate iterative calculation. The Windows path above should not be assumed to match Mac menus exactly.

Understand warnings, displayed values, and calculation mode

A circular-reference warning does not always mean Excel is unable to display any value. If iterative calculation is enabled, Excel may show a result from repeated calculation. If it is disabled, Excel can still display a previous or partial value in some situations, but that value should not be treated as proof that the loop is resolved. Recheck the formula dependencies and calculation settings before relying on the result.

Do not confuse iterative calculation with calculation mode. Automatic or manual calculation controls when Excel recalculates formulas; iterative calculation controls whether Excel permits repeated calculations for circular dependencies. Changing calculation mode alone does not remove a circular reference. Likewise, pressing F9 may recalculate formulas but will not fix an accidental loop.

When reviewing a workbook received from someone else, check whether iterative calculation is enabled and whether the model is documented. Calculation settings can influence how a workbook behaves, so a number that appears on one computer may be affected by settings or formula logic on another. If a result matters, trace its inputs and confirm the workbook’s intended calculation method instead of assuming the displayed number is reliable.

Frequently Asked Questions

Is a circular reference always an error?
No. It is an error when the loop is accidental or the model cannot calculate as intended. Some models deliberately use circular references with iterative calculation, but that requires clear logic and suitable calculation settings.

Why does Excel say there is a circular reference when my formula does not mention its own cell?
The reference may be indirect. Another cell used by the formula may depend on a third cell that eventually refers back to the original one. Trace the chain across formulas and worksheets until it returns to the starting cell.

How do I remove a circular reference without changing the intended result?
Identify the intended inputs, outputs, and calculation range first. Then correct the formula or layout so the output no longer feeds back into its own calculation. For a total, for example, exclude the total cell from the summed input range. Verify the result against a known example or an independent calculation.

Should I turn on iterative calculation to clear the warning?
Only if the circular dependency is intentional and the workbook is designed to calculate iteratively. Enabling it can mask accidental loops, so first inspect the formulas and confirm the expected result and stopping settings.

Can a circular reference involve more than one worksheet?
Yes. A formula on one sheet can depend on a cell on another sheet, whose formula eventually refers back to the first sheet. Use the Circular References list to start investigating, then follow each referenced cell across worksheets.

Why do I still see a circular-reference message after fixing a formula?
There may be another loop elsewhere in the workbook, or the changed formula may still be part of an indirect cycle. Check the Circular References list again, inspect the remaining addresses, and confirm that the corrected formula no longer leads back to itself.

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.