Excel Circular Reference Example and How to Fix It

Errors & fixes · Updated November 21, 2026

An Excel circular reference occurs when a formula refers to its own cell, either directly or through other formulas. For example, if A1 contains =A1+1, A1 depends on its own result, so Excel cannot calculate it normally; a common accidental example is a total formula that includes the cell holding the total.

A simple Excel circular reference example

Suppose a worksheet has nine expense amounts in A1:A9 and you want their total in A10. If A10 is included in the sum range, the total depends on itself. With the formula entered in A10, the cell layout is expense amounts in A1:A9 and the total in A10:

=SUM(A1:A10)

This is a circular reference because the range A1:A10 includes A10, where the formula itself is stored. If Excel is not set to use iterative calculation, this formula has no ordinary calculated total; Excel identifies the circular reference instead. The formula does not mean “add the values above and then add the total.” It asks Excel to calculate a value that includes that same not-yet-determined value.

To correct the range, leave the total cell out. With the same layout—nine amounts in A1:A9 and the result in A10—the corrected formula is:

=SUM(A1:A9)

If A1:A9 contain 12, 8, 15, 10, 5, 20, 7, 13, and 10, the result in A10 is 100. This is the usual fix: make sure a formula does not include its own cell, directly or indirectly.

Why circular references happen

A direct circular reference is easy to recognize: a formula points back to the cell that contains it. Indirect circular references are less obvious. For example, B2 might refer to C2, C2 might refer to D2, and D2 might refer back to B2. None of those formulas needs to mention its own cell by name, but together they form a dependency loop.

Excel normally calculates formulas by following their dependencies. If a value changes, it works out which formulas need recalculating and in what order. A loop breaks that order: Excel would need the result of one formula before it can calculate another, while that other formula ultimately needs the first result. Without a deliberate iterative method, there is no straightforward starting point.

Typical causes include:

  • A SUM range that accidentally includes the cell containing the SUM formula.
  • A formula copied into a row or column where its relative references shift into the formula cell.
  • Two or more formulas that refer to one another, often after editing a model.
  • A calculation designed to feed a result back into an input, such as some cash, tax, or interest models.

A circular reference is not automatically proof that a workbook is damaged. It can be intentional in a model that uses repeated calculations. But it is often an unintended range or reference error, so it is worth finding the cause before enabling iteration.

What Excel does when it finds one

In Excel for Microsoft 365 on Windows, a newly entered formula that creates a circular reference generally triggers a warning. The message indicates that one or more circular references exist and that a formula may not calculate correctly. Excel may also show Circular References on the status bar. Depending on the workbook and calculation state, a circular cell may display zero or a previously calculated value; do not treat that displayed number as proof that the formula is correct.

Excel can calculate circular formulas if iterative calculation is enabled. Instead of solving a loop in one pass, it recalculates the related formulas repeatedly. It stops when it reaches the iteration limit or when the change between iterations is small enough. The displayed answer is therefore an approximation shaped by the model and the iteration settings, not necessarily an exact mathematical solution.

Iteration is not a universal cure. Some loops settle toward a stable value, while others keep changing, oscillate between values, or produce a result that is sensitive to the starting values and settings. A workbook can also contain both intentional and accidental circular references, so enabling iteration may conceal a mistake.

How to find the circular cell

When Excel identifies a loop, use its circular-reference list to locate the cells involved. In Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References. If Excel has detected a circular reference, the menu lists a cell address; select it to move to that cell. If there are multiple loops, inspect the listed cells and their references one at a time.

Then inspect the formula bar and follow the references. A direct loop will show the formula referring to its own address. For an indirect loop, follow the chain: if B2 refers to C2, check C2, then continue until the chain returns to B2 or another cell already visited. Formulas > Trace Precedents can help show which cells feed a selected formula. Remove the arrows afterward with Formulas > Remove Arrows.

The Circular References menu may not be useful if Excel has not detected a loop in the current calculation state. Also check formulas that were recently copied, inserted, or changed, particularly totals at the edge of a data range. A formula may be circular even when the visible number looks plausible because that number could be a previous result.

Ways to fix an accidental circular reference

The right correction depends on what the formula is supposed to calculate. Start by identifying the intended inputs and output, rather than changing references until the warning disappears. Common repairs include:

  • Exclude the result cell from a total. If amounts are in A1:A9 and the total is in A10, sum A1:A9 rather than A1:A10.
  • Correct a copied reference. Check whether a relative reference shifted when a formula was filled down or across. Change the reference or use an appropriate absolute reference if the model requires a fixed input cell.
  • Separate inputs from calculations. Put assumptions or starting values in dedicated cells and outputs in different cells, so a calculated result is not also treated as its own input.
  • Break an accidental dependency chain. If two formulas refer to each other unintentionally, decide which cell should be the source value and which should calculate from it.

After editing, check the formula and its range, confirm that the warning is gone, and verify the answer using a small manual calculation or an independent method. If the workbook has several linked sheets, inspect references across sheets too; a circular loop can cross worksheet boundaries.

When iterative calculation is appropriate

Some models intentionally use feedback. For instance, a forecast might calculate interest from an ending cash balance while that balance itself includes the interest. If the model is designed around that relationship, repeated calculation may be appropriate. The important distinction is that the loop must be intentional, understood, and checked—not merely ignored because Excel displayed a warning.

To manage iterative calculation in Excel for Microsoft 365 on Windows, choose File > Options > Formulas, then under Calculation options select Enable iterative calculation. Excel provides controls for Maximum Iterations and Maximum Change. The commonly supplied defaults are 100 iterations and 0.001 maximum change, but check the workbook’s current values rather than assuming they are unchanged. Maximum Iterations sets a cap on repeated calculations; Maximum Change is the stopping tolerance for the change between results.

Use a sensible tolerance for the units and precision in the model. A tolerance that is too large can stop before the result is sufficiently stable; a very small tolerance may require more calculation and may not help a loop that fails to converge. Compare results under reasonable settings and test how the model responds to changed inputs. Document why iteration is enabled so another person can distinguish the intended loop from an error.

On Mac, use Excel > Settings (or Preferences in some releases) > Calculation to find calculation options; wording and layout can vary by release. Excel for the web may open and recalculate workbooks that use iterative settings, but use the desktop app when you need to inspect or change the iteration controls. If the loop is not genuinely required, removing it is safer and easier to maintain.

Common misconceptions and checks

A circular reference is not the same as a formula error such as #DIV/0! or #VALUE!. It describes a dependency relationship: a formula depends on its own result, directly or indirectly. The cell may show a number rather than a visible error, particularly when iterative calculation is active or a previous result is retained, so inspect the formula and Excel’s circular-reference information instead of relying only on the cell display.

Another misconception is that every reference to the same cell is circular. A formula can use other cells that depend on it only if the dependency path eventually returns to the formula cell. For example, a formula in A1 that reads B1 is not circular merely because B1 is nearby; it becomes circular if B1, perhaps through other formulas, also depends on A1.

Finally, turning on iteration does not tell Excel which value is correct. It supplies a calculation procedure for a loop. The formula logic, starting conditions, tolerance, and maximum number of repetitions still matter. If a result changes substantially when those settings change, investigate the model before relying on it.

Frequently Asked Questions

What is a circular reference in Excel?
A circular reference is a formula dependency loop. A formula may refer to its own cell directly, or rely on another formula that eventually refers back to it.

What is a simple example of a circular reference?
If A1 contains =A1+1, the formula uses A1 to calculate A1. A SUM formula that includes the cell where the SUM is entered is another common example.

How do I find circular references in Excel?
On Windows, select Formulas > Error Checking > Circular References, then choose a listed cell. Follow its formula references to find whether the loop is direct or passes through other cells.

Should I enable iterative calculation?
Only when the circular dependency is intentional and the model is designed to use repeated calculations. For an accidental loop, correct the formula instead; iteration can otherwise hide the underlying mistake.

Why does Excel show a number for a circular formula?
The number may reflect iterative calculation or a previously calculated value. A displayed value alone does not confirm that the formula is correct; check the circular-reference list and the workbook’s calculation settings.

Related

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.