Excel Circular Reference Errors: Find and Fix Them

In Excel, a circular reference occurs when a formula depends on its own result, either directly or through a chain of other cells. To find one in Microsoft 365 for Windows, select Formulas > Error Checking > Circular References, then inspect the listed cells and change the formulas so the dependency loop is broken—or enable iterative calculation only if the loop is intentional.

What the circular reference warning means

Excel normally calculates formulas by following their dependencies: it works out the cells a formula needs, then uses those results to calculate the formula. A circular reference interrupts that sequence. Excel reaches a cell whose value depends, directly or indirectly, on the value it is currently trying to calculate.

The warning may say that there are one or more circular references and that a formula refers to its own cell either directly or indirectly. This does not necessarily mean the workbook is damaged. It does mean Excel cannot calculate the affected formulas normally unless you remove the loop or deliberately allow repeated calculations.

A circular reference is different from a #REF! error. A #REF! error usually means a formula contains an invalid cell reference, often because a referenced row, column, or cell was deleted. A circular reference is a dependency problem: the referenced cells exist, but their formulas lead back to the starting cell.

Direct and indirect circular references

A direct circular reference is the easiest kind to recognize: a formula in a cell includes that same cell as an input. For example, suppose cell A1 is meant to contain a number, but instead you enter this formula in A1:

With the formula entered in cell A1, the cell refers to itself:

=A1+1

Excel cannot calculate a normal answer because A1 would need to know its own value before it could add one. A common cause is accidentally including a total cell in its own SUM range, such as putting a total in A10 and summing A1:A10.

An indirect circular reference involves two or more cells. For example, suppose B2 and C2 are intended to hold separate calculations:

With the formula entered in B2:

=C2+10

With the formula entered in C2:

=B2*2

B2 depends on C2, while C2 depends on B2. Neither value can be calculated independently, so the loop runs through both cells. Longer chains can involve many formulas, sheets, or named ranges, making the starting point less obvious.

How to locate circular references

On Excel for Microsoft 365 for Windows, use the Circular References command on the Formulas tab. Excel lists cell addresses it has identified; select an address to move to that cell. If there are multiple loops, work through the listed references and check again after changing each one. The list can be more useful than simply looking at the cell that first displayed the warning, because the source may be several steps away.

Open Formulas > Error Checking > Circular References and select a listed cell.
Read the formula bar and identify the cells that formula uses.
Follow those dependencies, checking formulas in each referenced cell for a path back to the original cell.
Correct the dependency or, if the calculation is intentionally iterative, decide whether iterative calculation is appropriate.

Excel may also show Circular References and a cell address on the status bar. Treat that as a clue, not a complete map: a workbook can contain more than one loop, and a loop can cross worksheets. The Formulas menu and careful inspection of related formulas help you investigate further.

Formula Auditing can help make a complicated dependency chain visible. Select a formula cell and use Formulas > Trace Precedents to see cells that feed into it. Follow the arrows from one cell to another and look for a route back to the starting cell. Trace Dependents shows cells that use the selected cell’s result. These tools show relationships, but they do not automatically repair the formulas.

How to fix an accidental circular reference

First decide what each formula is supposed to calculate. Then make the dependency flow in one direction: input cells feed calculation cells, and calculation cells feed output or total cells. The right correction depends on the intended calculation; deleting a formula just to remove the warning can silently produce an incorrect workbook.

For a total that includes itself, shorten the range so it includes only the detail rows. For a formula that accidentally points to its own column or row, correct the cell reference. If two formulas refer to each other because a calculation has been split incorrectly, move one calculation to a separate input or helper cell so neither formula relies on its own output.

When the intended value is a running total, use a previous-row result rather than a total that includes the current result. For example, suppose A2:A4 contains values 5, 7, and 3, and B1 is a starting balance of 0. Enter this formula in B2 and fill it down through B4:

With starting balance in B1 and the current row’s amount in column A:

=B1+A2

The results in B2:B4 are 5, 12, and 15. Each row uses the previous row’s total and its own amount; none of the formulas depends on the result it is currently calculating. If you instead put a total in a cell included in its own SUM range, revise the range to cover only the detail cells.

When iterative calculation is appropriate

Some models deliberately use a circular relationship and rely on repeated calculations to approach a stable result. In that case, Excel’s iterative calculation setting can be appropriate. It is not a general-purpose way to dismiss a warning: an unintended loop can return misleading values once iteration is enabled.

To turn it on in Excel for Microsoft 365 on Windows, select File > Options > Formulas, check Enable iterative calculation, and set Maximum Iterations and Maximum Change. Excel repeats calculations until it reaches the iteration limit or the change between results falls below the chosen threshold. A larger iteration limit can allow more passes; a smaller maximum change asks for closer convergence but may require more work.

For example, suppose A2 contains 100, and B2 and C2 are intentionally defined in terms of one another:

With the formula entered in B2:

=C2*0.5

With the formula entered in C2:

=(A2+B2)/2

With iterative calculation enabled, the values converge toward about 33.3333 in B2 and 66.6667 in C2. These figures satisfy both relationships: B2 is half of C2, and C2 is the average of 100 and B2. The displayed result depends on the iteration settings and the workbook’s calculation behavior, so a finite iteration limit may leave a small difference from the exact values.

On Excel for Mac, calculation preferences are under Excel > Preferences or Excel > Settings > Calculation, depending on the version; look for the iterative-calculation option and its limits. Excel for the web has fewer calculation controls than the desktop apps, so if you need to change workbook iteration settings, open the workbook in the desktop version. Iterative calculation is a workbook-level setting, so review its effect on the workbook rather than treating it as a setting for just one formula.

Common causes and troubleshooting

Circular references often appear after editing a formula, copying a formula into a new row, or changing a SUM range. They can also arise when a formula is meant to use an input but accidentally points to the output cell, or when calculations on different sheets refer back to each other.

Check totals: confirm that a total cell is not included in the range it sums.
Check copied formulas: inspect relative references that may have shifted into the formula’s own row or column.
Check other sheets: follow references across worksheet tabs, not only within the visible sheet.
Check names: review named ranges used in the formula if the visible cell references do not explain the loop.
Check recent changes: undo or compare a recent formula edit if the warning began after a specific change.

If Excel’s list does not make the whole loop obvious, inspect formulas one link at a time and use Trace Precedents where it is helpful. Hidden rows or columns do not remove a dependency, and a formula can still depend on cells that are not currently visible. Also check whether a workbook already has iterative calculation enabled: that setting can allow a circular model to calculate without making the underlying loop accidental or correct.

Preventing the error in shared workbooks

Separating inputs, intermediate calculations, and outputs makes circular dependencies easier to avoid and diagnose. A dedicated input area helps prevent a formula from replacing a value it is supposed to use. Clear labels and consistent worksheet layouts also make it easier for another person to check whether a formula points to the intended cells.

Before sharing a workbook, review the Circular References menu and confirm that any remaining loops are deliberate and documented. If iteration is needed, note why it is enabled and what the iteration limits mean for the model. This is especially useful for collaborators who might otherwise disable the setting, change it, or assume that a circular reference is an accidental mistake.

Frequently Asked Questions

Is a circular reference always an error?
No. It is usually accidental, but some models intentionally use iterative calculation. If the loop is intentional, document the reason and check that the results converge as expected.

Why does Excel still show a circular reference after I fix one?
The workbook may contain another loop, or the edited formula may still connect back to itself through other cells. Reopen Formulas > Error Checking > Circular References and investigate each listed address.

Can a circular reference involve different worksheets?
Yes. A formula on one sheet can refer to a cell on another sheet, whose formula eventually points back to the first. Follow the references across sheets until you find the link that closes the loop.

Should I enable iterative calculation to remove the warning?
Only if the circular relationship is intentional and repeated calculation is part of the model. For an accidental loop, correct the formulas instead; enabling iteration can conceal a problem and produce results that look plausible but are not intended.

What is the difference between a circular reference and #REF!?
A circular reference means formulas depend on their own results. #REF! means a formula contains an invalid reference, often because a referenced cell or range was deleted. The causes and fixes are different.

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.