Circular Reference in Excel: How to Locate and Fix It

Errors & fixes · Updated November 21, 2026

To locate a circular reference in Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References, then click a listed cell address to jump to it. Also check the status bar at the bottom of the window: it may show “Circular References” and a cell address. Once you find the cell, inspect its formula and trace the cells it depends on to identify the loop.

Use the Error Checking menu first

The Error Checking menu is usually the quickest way to navigate to a circular reference in the desktop version of Excel. Open the Formulas tab, select Error Checking in the Formula Auditing group, and point to or open Circular References. Excel displays the address of a cell involved in a circular calculation. Select an address to make that cell active on its worksheet.

If the workbook has more than one circular reference, the menu can show multiple addresses. Work through the listed cells rather than assuming the first one is the only problem. A cell in the list is an entry point into the cycle; the whole chain may include other cells that are not listed. Excel may also identify a circular reference on a different worksheet, so note the sheet name as well as the cell address.

After navigating to a cell, look at the formula bar and the cell’s row and column headings. If the formula is long, copy it into a temporary text editor or inspect its references piece by piece. Do not overwrite the formula just to dismiss the warning: first determine whether the loop is accidental or an intentional part of the model.

Check the status bar

Excel can report a circular reference on the status bar at the bottom of the application window. Depending on the active sheet and workbook state, the message may include an address. Treat it as a useful clue, not a complete inventory: the Error Checking menu is better for navigating among identified circular-reference cells, particularly when the workbook has several sheets.

If you cannot see the status bar, the Excel window may be too small, or the bar may have been hidden or customized. The status bar can be customized by right-clicking it, but its available indicators depend on the current context. Use the ribbon command as the main route rather than relying on a status-bar message always being visible.

What Excel means by a circular reference

A formula has a circular reference when its calculation depends, directly or indirectly, on its own result. In a direct circle, the formula refers to the cell that contains it. For example, with a value in C2 and a formula entered in B2, the following formula refers back to B2 itself:

With a value in C2 and the formula entered in B2, this is a direct circular reference: =SUM(B2,C2).

In an indirect circle, two or more formulas lead back to one another. For instance, if A1 contains =B1+1 and B1 contains =A1+1, each cell needs the other cell’s result before it can calculate its own. The chain can be longer: A1 may depend on B1, B1 on C1, and C1 on A1. A circular reference can also cross worksheets.

Ordinary spreadsheet formulas are evaluated from their inputs. A cycle removes the clear starting point, so Excel normally warns that a circular reference exists and may stop calculating the affected result as expected. It is not the same as a formula error such as #VALUE!: the issue is the dependency structure, even if the cell currently displays a number or an older result.

Trace the full dependency loop

Finding one cell in the cycle is only the beginning. Select it, then use Formulas > Trace Precedents to show cells that feed its value. Follow the arrows and inspect the formulas in those cells. If you need to see which cells rely on the selected cell, use Formulas > Trace Dependents. These tools can help reveal a chain that is not obvious from the worksheet layout.

  • Start from the address listed under Circular References and read its formula from the formula bar.
  • Follow each referenced cell, checking whether its formula points back to the starting cell or to another cell in the chain.
  • Use Trace Precedents or Trace Dependents when the references are spread across distant parts of the sheet.
  • When the arrows no longer help, select Formulas > Remove Arrows and continue by inspecting formulas and referenced cells.

For a more detailed calculation check, select Formulas > Evaluate Formula. The dialog steps through the formula’s evaluation, which can help explain how references are being interpreted. It does not automatically repair a cycle, and it may not explain every relationship in a complex workbook. Named ranges and table references can make formulas less visually obvious, so check Formulas > Name Manager if a formula uses a name rather than a cell address.

Look for common causes

Many circular references come from a small selection or a misplaced formula. A total formula might accidentally include its own total cell, or a formula intended for one row might be copied into a summary row that it references. Review range endpoints carefully: when a total is in the same column as the values being summed, make sure the range stops above the total cell.

Other likely causes include a formula copied across columns with a relative reference that shifted unexpectedly, a formula that points to a named range which includes the formula cell, and links between worksheets that eventually return to the original sheet. A loop may also be introduced when someone changes a formula or moves cells; the circular relationship can be difficult to spot if the affected cells are far apart.

Check hidden rows, columns, and worksheets too. Hiding a cell does not remove it from a calculation, and a formula on a hidden sheet can participate in the cycle. If the Error Checking list points to a surprising location, unhide relevant areas and inspect any names and cross-sheet references before changing anything.

Fix an unintended circular reference

If the loop is accidental, change the formula logic so the calculation has a clear input and output. A common correction is to move a total outside the range it totals, or to revise a formula so it points to the intended input cell rather than to itself. Before editing, save a copy of the workbook or record the original formula. This makes it easier to compare results and reverse an edit if the workbook contains linked calculations.

For example, suppose B2:B5 contains four amounts and B6 is meant to show their total. With the amounts in B2:B5 and the total formula in B6, the intended formula is =SUM(B2:B5). If the total formula is instead placed in B5 while summing B2:B5, B5 includes its own result and creates a direct circle. Put the total in B6 or shorten the range so it includes only the intended input cells.

After changing the formula, return to Formulas > Error Checking > Circular References. If the menu still lists a cell, follow the remaining reference chain; correcting one loop does not guarantee that every loop in the workbook has been removed. Then check related totals and outputs to make sure the repair has not changed the intended calculation.

When iterative calculation is appropriate

Not every circular reference is a mistake. Some financial or engineering models intentionally use a repeated calculation in which a result feeds back into an input. In that case, Excel can recalculate iteratively rather than stopping at the first cycle. Use this only when the model is designed for iteration and you understand the expected convergence; turning it on simply to silence a warning can conceal a faulty formula.

On Windows, open File > Options > Formulas and select Enable iterative calculation. Excel provides settings for the maximum number of iterations and the maximum change. Iteration ends when the calculation reaches the specified limit or meets the change condition. The appropriate values depend on the model, so there is no universal setting that makes every circular model reliable.

Before enabling iteration, document why the cycle is required, check that the formulas move toward a sensible result, and compare outputs with an independent calculation where possible. Iterative calculation can make a workbook’s results depend on its calculation settings and starting values. If colleagues use the file, tell them that iteration is intentional and make sure they understand the same settings are needed to interpret the model.

Excel for Mac and Excel for the web

In Excel for Mac, the desktop Formula Auditing tools are also available; look on the Formulas tab for Error Checking and Circular References. The names and layout can vary slightly with the Excel version. Iteration settings are under Excel’s calculation preferences: depending on the release, open Excel > Settings or Excel > Preferences, then choose Calculation.

Excel for the web can display workbook calculation issues, but its interface and auditing tools are not identical to the Windows desktop application. If you cannot find the circular-reference list or need to trace a complicated chain, open the workbook in the desktop app and use the Formulas tab. When working in a shared file, avoid making an unverified formula change while other people are editing it; coordinate the repair and confirm the workbook recalculates as expected.

Frequently Asked Questions

Why does Excel say there is a circular reference when I cannot see one?
A formula can refer to itself through several other cells, across sheets, or through a named range. Use the Circular References list to jump to a starting cell, then trace its precedents and inspect names in Name Manager. Hidden rows, columns, or sheets can contain part of the chain.

Does a circular reference always mean the formula is wrong?
No. It is often accidental, but some models intentionally use iterative calculations. If the workbook was not designed for iteration, treat the warning as something to investigate rather than enabling iteration immediately.

How do I find every circular reference in a workbook?
Open Formulas > Error Checking > Circular References and work through the addresses Excel lists. After correcting a cycle, check the menu again. Continue until no unintended circular-reference entries remain, and test important outputs because one correction may reveal another issue.

Can I fix a circular reference by turning on iterative calculation?
Only if the circular calculation is intentional and the formulas are designed to converge. Iteration changes how Excel handles the loop; it does not correct an accidental self-reference or prove that the resulting number is valid.

Why does the circular-reference warning return after I fix it?
There may be another loop, or the edited formula may still connect indirectly to itself. Reopen the Circular References list, follow the listed cells, and check cross-sheet links and named ranges. Also confirm that the change was saved to the workbook you are currently viewing.

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.