An Excel circular reference happens when a formula depends on its own result, directly or through other cells.
Learn how to find the loop, fix common formula mistakes, and decide when iterative calculation is appropriate.
What is a circular reference in Excel?
A formula creates a circular reference when its calculation path eventually leads back to the cell containing that formula.
For example, if cell B2 contains =B2+1, Excel cannot calculate a starting value because B2 depends on itself.
The loop can also involve multiple cells.
If C2 contains =D2+5 and D2 contains =C2*2, calculating either cell requires calculating the other first.
Excel may display a circular reference warning, show a value of zero, or leave a previous result in place, depending on the workbook and calculation settings.
How to find a circular reference in Excel
Start with Excel’s built-in circular reference indicator.
In desktop Excel for Windows, open the Formulas tab, select Error Checking, and point to Circular References.
The menu lists a cell involved in a detected loop; select an entry to jump to it.
If the workbook contains multiple loops, Excel may reveal them one at a time as you resolve the reported references.
On Mac, look for Circular References under Error Checking on the Formulas tab.
Menu labels and locations can vary slightly by Excel version.
Excel for the web may show a warning, but its tools for tracing complex formula dependencies can differ from the desktop app.
If the list is not available, open the workbook in desktop Excel and inspect the formulas there.
Once you reach a reported cell, use Formulas > Trace Precedents and Trace Dependents to view arrows between related cells.
These tools can help expose an indirect loop across a sheet.
For workbooks with many formulas, inspect each reported cell and follow its referenced cells until the path returns to the original cell.
How to fix common circular reference errors
Remove the formula’s reference to its own cell
A common mistake is placing a total formula inside the range it totals.
Suppose cells B2:B6 contain amounts and B6 is intended to display their total.
Entering =SUM(B2:B6) in B6 includes the formula cell in its own sum.
Put the total in B7 instead and enter =SUM(B2:B6).
If the total must be in B6, move the input values elsewhere and update the range so it excludes B6.
Check relative references after copying formulas
Copying or filling a formula can shift a reference unexpectedly.
For instance, a formula copied down may change from =A2*B1 to =A3*B2, even if the rate in B1 should remain fixed.
That shift can create a loop when the formula is copied into the rate cell or another dependent cell.
Use an absolute reference such as $B$1 when a formula should always refer to the same cell.
For example, enter =A2*$B$1 and fill it down.
Absolute references prevent the reference from moving during copying; they do not resolve a circular reference if the fixed cell itself depends on the formula.
Review formulas that depend on each other
For a loop involving multiple cells, draw the dependency chain in order.
If C2 contains =D2+5 and D2 contains =C2*2, decide which cell should contain an input and which should calculate from it.
For example, make C2 an entered value of 10 and keep D2 as =C2*2; the expected result in D2 is 20.
Do not replace a formula with a hard-coded value unless that matches the intended model.
If the relationship between the cells is unclear, check the workbook’s labels, notes, and source data before changing the dependency.
When iterative calculation is appropriate
Some financial and engineering models intentionally calculate through a circular relationship, such as interest that depends on a balance that itself includes interest.
In these cases, Excel can repeat calculations until results change by less than a chosen amount or until it reaches a maximum number of iterations.
In desktop Excel for Windows, open File > Options > Formulas, then enable Enable iterative calculation.
On Mac, find calculation settings under Excel > Preferences or Settings, depending on the version.
Set the maximum iterations and maximum change to values appropriate for the model.
A smaller maximum change generally demands closer convergence, while a higher iteration limit allows more repeated calculations.
Only enable iteration when the workbook’s logic requires it and you understand the expected result.
Iterative formulas can produce different values when settings change, may converge slowly, or may fail to settle.
Workbooks shared with others can also behave differently if their Excel settings are not the same.
Record the intended settings and verify the model against a known example.
What if Excel still reports a circular reference?
- Check every worksheet. A formula on one sheet can refer to a cell on another sheet that eventually points back.
- Inspect named ranges. A formula may use a name that refers to the formula cell or to a range containing it.
Review names in Formulas > Name Manager. - Look for hidden rows, columns, and sheets. They can contain formulas that complete a dependency loop.
- Check recently changed formulas. Use Undo if the warning began after an edit, or compare the affected formula with a known working version.
- Recheck after each change. Excel may report another loop only after the first one is removed.
Excel’s circular reference tools and formula syntax are available in current desktop versions of Microsoft Excel for Windows and Mac, though menu wording can vary.
Google Sheets also reports circular dependencies and offers an iterative calculation setting in its calculation preferences; its controls and behavior are separate from Excel’s, so confirm the settings when moving a workbook between the two applications.