An Excel circular reference occurs when a formula refers back to its own cell, either directly or through other cells; it is not a separate Excel function. Excel normally flags the loop instead of calculating it as an ordinary formula. To allow an intentional circular calculation in Microsoft 365 for Windows, go to File > Options > Formulas and select Enable iterative calculation; for an accidental loop, change the formula or its references to break the cycle.
What a circular reference means
A formula needs inputs from cells, and Excel calculates formulas by following those relationships. A circular reference exists when that chain eventually returns to the formula’s starting cell. For example, a formula in A1 that uses A1 as an input is a direct circular reference. If A1 depends on B1 and B1 depends on A1, the loop is indirect, even though neither formula refers to itself by address.
This is a dependency problem, not a special calculation function. There is no built-in function called CIRCULAR that you add to a formula. Ordinary functions such as SUM or IF can be involved, but the circularity comes from the references between cells. It can occur in a simple worksheet, a large model, or a workbook that combines formulas across sheets.
Excel usually warns about a circular reference when you enter or edit a formula that creates one. With iterative calculation turned off, the formula cannot be calculated through the normal dependency process. Depending on the workbook and calculation state, a cell may show a warning-related value or a previous value, so do not treat a displayed number as proof that the formula is working correctly.
How circular references happen
Many circular references are accidental. A common cause is including a total cell in the range being totaled. If a list occupies B2:B9 and its total is in B10, a total formula that includes B10 asks Excel to include the answer in calculating that same answer. A copied formula can create the same issue if a relative reference shifts into the formula cell. Other causes include formulas that point to the wrong row, pasted formulas, and dependencies that cross between worksheets.
Some loops are less obvious because they pass through several cells. For example, C4 may depend on D4, D4 on E4, and E4 on C4. Named ranges and formulas on other sheets can also hide a link in a chain. A workbook can therefore develop a circular reference after a change that seems unrelated to the cell Excel identifies.
- Direct loop: a formula refers to its own cell.
- Indirect loop: two or more formulas form a dependency chain that returns to the first cell.
- Range-edge mistake: a SUM or other formula includes its own result cell.
- Copied-reference mistake: a copied formula shifts a reference into an unintended location.
How to find the circular cell
Start with the warning Excel displays when the loop is created. In the Windows desktop app, you can also select Formulas > Error Checking > Circular References. The menu lists a cell address to inspect; if more than one loop exists, follow the listed references and revisit the menu as needed. The status bar may also indicate that the workbook contains a circular reference.
Once you reach a listed cell, inspect the formula bar and follow the references it uses. Formulas > Trace Precedents can show cells that feed into the selected formula, while Trace Dependents can show cells that rely on it. These arrows help reveal indirect chains; remove them with Formulas > Remove Arrows when you are finished. If a reference crosses sheets, open the referenced sheet and continue tracing there.
Be alert for less visible causes, including named ranges, formulas inside tables, and references to cells on another worksheet. If the listed cell does not appear to refer to itself, trace its inputs one step at a time. A circular reference is a cycle in the dependency graph, so the closing link may be several formulas away from the cell Excel first reports.
How to fix an accidental loop
Usually the safest correction is to make the formula express the intended calculation without referring to its own result. Suppose B2:B9 contains eight amounts and B10 is the total. If the range includes B10, the total cell becomes part of its own input. With values 12, 8, 15, 10, 5, 20, 7, and 3 in B2:B9, the self-referential version would be =SUM(B2:B10); that loop does not produce a valid total with iterative calculation disabled. With those same values in B2:B9 and the total in B10, use =SUM(B2:B9), which returns 80 because the eight amounts add to 80.
Other corrections depend on the model. You might fix an incorrect cell address, shorten an input range, move a subtotal outside the range it summarizes, or separate an intermediate calculation from a final result. Before editing a complicated workbook, save a copy. Then check whether other formulas depend on the cell you plan to change, and confirm that the revised formula still reflects the worksheet’s intended logic.
Do not switch on iterative calculation simply to hide an unexpected warning. It can make a circular formula return a number without correcting the underlying mistake. First decide whether the cycle is intentional and whether the mathematical method is appropriate. If it is not intentional, break the loop and recalculate the workbook.
When iterative calculation is appropriate
Some models deliberately use a circular relationship and refine an estimate repeatedly. In those cases, Excel’s iterative calculation setting allows formulas to be recalculated until the result changes by less than a chosen amount or the calculation reaches a set number of iterations. In Excel for Microsoft 365 on Windows, use File > Options > Formulas > Enable iterative calculation. The settings include Maximum Iterations and Maximum Change. Excel’s default values are 100 iterations and 0.001 for maximum change; a workbook may have different settings.
On Excel for Mac, iterative calculation is available in Excel > Preferences (called Settings in some releases) > Calculation, where you can select Use iterative calculation and adjust the limits. Menu labels can vary slightly by release. Iteration settings affect how circular formulas calculate, so check them when opening a workbook from someone else rather than assuming your local settings match the author’s.
For a small mathematical demonstration, suppose A1 contains the fixed value 100 and A2 is intended to be a total that includes 10% of itself. With that layout, enter =100+A2*10% in A2. The equation is A2 = 100 + 0.1 × A2, whose exact mathematical solution is about 111.111; with iterative calculation enabled, Excel approaches roughly 111.11, with the displayed result depending on the iteration limits and precision settings. This illustrates convergence, not a recommended method for every business calculation.
Iteration is useful only when the model is designed to converge to a meaningful answer. A loop that keeps growing, oscillates, or depends heavily on its starting values may stop at the iteration limit without reaching a trustworthy result. Choosing the maximum change and iteration count requires understanding the model’s logic, not just selecting larger numbers.
Common problems and misconceptions
A circular reference is not automatically an error in the sense that every model containing one is wrong. It is a calculation structure that Excel cannot resolve in the usual one-pass dependency order. Some financial, engineering, and forecasting models intentionally use iterative methods, but those models need clear assumptions and tested settings. In ordinary worksheets, a circular reference is more often a sign of a mistaken range or dependency.
Another misconception is that turning on iteration guarantees an exact answer. It does not. Excel repeats calculations under the limits you set, and the result may be an approximation. Maximum Change controls the threshold for considering successive results sufficiently close; Maximum Iterations limits how long Excel keeps recalculating. A small threshold can require more work, while a low iteration limit can stop the process before it settles.
Also distinguish a circular reference from a formula error such as #REF!. A #REF! error indicates an invalid reference, often because a referenced cell or range was deleted. A circular reference instead describes a valid cell address that participates in a loop. They call for different fixes: restore or replace an invalid reference for #REF!, and inspect the dependency cycle for a circular reference.
Performance and safer workbook practices
Repeated recalculation can make a workbook slower, especially when loops involve many formulas, volatile functions, large ranges, or several linked sheets. Results can also be harder for another person to interpret if the workbook silently relies on nondefault iteration settings. Add a note explaining why a loop exists and what the chosen iteration limits mean, particularly in shared or long-lived workbooks.
For a more maintainable model:
- Keep inputs, intermediate calculations, and outputs in clearly separated cells or sections.
- Use labels and comments to document any intentional circular relationship and its assumptions.
- Test the workbook with representative inputs, including boundary cases, and check whether results settle as expected.
- Review calculation settings after copying sheets or receiving a workbook from another person.
- Keep a noncircular version or a saved backup before making structural changes to a complex model.
These practices do not make a poorly designed loop safe, but they make its purpose easier to verify. If a model’s answer changes dramatically when you slightly adjust the iteration threshold, investigate the underlying assumptions before relying on the output.
Alternatives to a circular formula
If iteration is not essential, restructure the calculation so each formula depends only on earlier inputs or intermediate results. A separate helper cell can hold a value that would otherwise be embedded in a loop. In some models, an explicit input cell is clearer than attempting to calculate that input from a formula that depends on its own output.
For a calculation that genuinely requires solving an equation, consider whether it can be rearranged algebraically into a direct formula. For more involved optimization problems, Excel’s Solver add-in may be more appropriate than a circular reference, depending on the problem and available add-ins. Solver is a separate optimization tool, not a replacement for every iterative calculation, and its model still needs sensible constraints and validation.
The best choice depends on the actual relationship. A straightforward total should be fixed by correcting its range. A genuine iterative model may keep a circular structure, provided the calculation converges and the workbook documents its settings. Choosing between those approaches starts with tracing the dependencies and stating what the output is supposed to represent.
Frequently Asked Questions
Is a circular reference a function in Excel?
No. It is a cycle in the cell references used by one or more formulas. Any ordinary formula or function can participate in that cycle, but there is no special circular-reference function to insert.
How do I find a circular reference in Excel?
In the Windows desktop app, select Formulas > Error Checking > Circular References and inspect the listed cell. Trace its precedents and dependents if the loop passes through other cells or worksheets.
Should I enable iterative calculation?
Only when the circular relationship is intentional and the model is designed to converge. If the loop is accidental, correct the formula instead; enabling iteration can mask the problem rather than solve it.
Why does Excel show a circular reference warning?
At least one formula depends on its own result, either directly or through a chain of other cells. Check ranges, copied references, named ranges, and formulas on other sheets to find the link that closes the cycle.
Can a circular reference give an incorrect answer?
Yes. An accidental loop may not calculate as intended, and an intentional loop can still fail to converge or stop at the iteration limit. Verify the model’s logic, calculation settings, and results before relying on them.
Related
- Excel Slicer: Change a Vertical List to Horizontal
- How to Protect a Sheet in Excel and Set Permissions
- Excel Print Area: Set It, Adjust Pages, and Print Clearly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Inserting Rows? Fix the Most Common CausesIf Excel is not inserting rows, first select the worksheet row header where the new row should go,…
- Excel Autofill Not Working? Fix Common Fill Handle IssuesIf Excel Autofill is not working, first enable the fill handle under File > Options > Advanced, then…
- Excel Error Symbols: What They Mean and How to Fix ThemExcel’s error symbols are values such as #VALUE! and #DIV/0! that explain why a formula cannot return a…
- Excel XLOOKUP Spill Error: Causes, Fixes, and ExamplesAn Excel XLOOKUP spill error usually means the formula is returning multiple results but Excel cannot place them…
- XLOOKUP Excel IF ERROR: Fix Missing Matches and ErrorsTo show a friendly result when XLOOKUP cannot find a match, use its fourth argument: with lookup value…
- Error Bars on an Excel Scatter Plot: A Clear GuideIn Microsoft Excel for Microsoft 365 on Windows, select the scatter chart, then choose Chart Design > Add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.