Excel Circular Reference Error: How to Find and Fix It

If Excel keeps showing a circular reference error, find the formula loop and change one reference so the formula no longer depends on its own result. In Microsoft 365 for Windows, start with Formulas > Error Checking > Circular References, then select a listed cell to jump to it; enable iterative calculation only when the loop is intentional.

What a circular reference means

A formula has a circular reference when its result depends, directly or indirectly, on the value it is trying to calculate. Excel cannot resolve an ordinary formula if calculating that formula requires its own unfinished result. A direct loop happens when a cell refers to itself. An indirect loop involves two or more cells that eventually lead back to the starting cell.

For example, with a formula entered in A1, this direct self-reference creates a loop:

With A1 selected as the cell containing the formula, enter =A1+1.

A formula can also include its own cell as part of a range. If C10 is the total cell and the range includes C10, it is circular:

With values in C2:C9 and the total intended for C10, enter =SUM(C2:C10) in C10.

The correction is to exclude the total cell, using the range C2:C9 instead. The issue is not that Excel refuses to use formulas in totals; it is that the formula’s input range includes the cell that must hold its output.

Find the cell Excel identifies

When Excel detects a circular reference, it may show a warning dialog with a message that there are one or more circular references. After closing the dialog, check the status bar at the bottom of the Excel window; it can identify a circular-reference cell. The ribbon command is often the quickest way to locate it:

Open the Formulas tab.
Select Error Checking in the Formula Auditing group.
Point to Circular References and select a cell address in the list.

Excel should take you to that cell. If more than one address appears, inspect and correct them one at a time. After fixing a loop, check the menu again: a remaining address may indicate another loop, or a second loop that was previously difficult to spot. The menu and status-bar indication can depend on the active workbook and sheet, so inspect the actual formulas rather than assuming the first listed cell is the only problem.

If you cannot find the command or the warning has disappeared, save the workbook and recalculate it with Formulas > Calculate Now. On Windows, F9 also recalculates open workbooks. Recalculation does not repair a circular reference, but it can make Excel surface the warning again. If the workbook uses manual calculation, check Formulas > Calculation Options; manual mode can leave results out of date, though it does not itself create a circular reference.

Trace the formula chain

Finding the flagged cell is only the first step. Select it and read the formula bar, then follow every referenced cell until you find the path that returns to the original cell. A reference may point to another sheet, so check the sheet name in the formula and inspect that destination as well.

Excel’s Formula Auditing tools can help. Select the suspect cell, then use Formulas > Trace Precedents to show cells that feed into it. Use Trace Dependents to see cells that rely on it. Repeat the tracing on referenced cells when the route is not obvious. Remove Arrows clears the tracing marks. These arrows help map direct relationships, but they do not automatically explain every indirect dependency or fix the loop.

For a workbook with many formulas, Formulas > Show Formulas displays formulas on the sheet instead of their calculated results. On Windows, Ctrl+` (the key often shares a location with the tilde) toggles this view. You can also use Home > Find & Select > Find and search within formulas for a cell address or sheet name. Be aware that a simple text search may find unrelated references and will not prove that a loop exists.

Also inspect defined names through Formulas > Name Manager. A formula can refer to a name whose definition points back to the original cell, making the cycle less visible in the formula bar. In models that use tables, review calculated-column formulas and structured references too. A formula that looks like a normal row calculation can still refer back to a total or another calculated column that leads back to it.

Common causes in real workbooks

Most accidental circular references come from a misplaced cell reference, a copied formula, or a total that includes itself. They often appear after inserting rows or columns, filling formulas across a range, or editing a template without checking which cell holds the result.

Totals inside their own ranges: A SUM formula in a bottom-row total may accidentally include that total row. Check the first and last cells in the range.
Copied formulas: Relative references shift as a formula is filled down or across. One row can end up referring to a cell that points back to it.
Cross-sheet loops: A summary sheet may reference a detail sheet whose formula, in turn, refers to the summary.
Inputs mixed with outputs: A cell intended for a user-entered value may be overwritten with a formula that uses that same cell as an input.
Names and table formulas: A defined name or structured reference can hide the return path between cells.

For instance, imagine a budget sheet where a total is calculated from a range that includes the total cell, while a second sheet uses that total to calculate a value in the original range. The problem then spans sheets, even though neither formula may look self-referential on its own. Trace the chain in both directions rather than changing a formula just because it is the first one Excel identifies.

Break an unintended loop without damaging the model

Before editing a complex workbook, save a separate copy. Then determine what each cell is meant to represent: an input, an intermediate calculation, or an output. Change the formula that violates that design, not simply the cell named in the warning. For a self-including total, adjust the range; for a shifted reference, restore the correct row or column; for a cross-sheet loop, decide which sheet should supply the input and which should calculate the output.

After the edit, compare the result with a small hand-checkable example or a known value. Confirm that dependent totals and charts still make sense, and check the Circular References menu again. If several formulas were changed, recalculate and review affected sheets before sharing the file. Replacing a formula with a typed number can make the warning disappear, but it also removes the calculation; do this only if a fixed value is genuinely intended.

A circular-reference warning is not the same as every other Excel formula error. Errors such as #REF! or #VALUE! have different causes. A broken reference might be part of a circular chain, but fixing that reference does not always resolve the loop. Follow the dependency path and verify that no route leads back to the formula being calculated.

When iterative calculation is appropriate

Some financial, engineering, or planning models intentionally use circular formulas to refine an estimate. In those cases, Excel can recalculate formulas repeatedly until values stabilize or it reaches a set limit. This is called iterative calculation. It is a modeling choice, not a general repair for an accidental loop.

To allow iteration in Microsoft 365 for Windows, choose File > Options > Formulas. Under Calculation options, select Enable iterative calculation. You can set Maximum Iterations, which limits the number of calculation cycles, and Maximum Change, which controls the stopping threshold for changes between cycles. Smaller maximum-change values generally require closer convergence, but can take more calculation work. Choose limits that suit the model and validate the resulting values independently.

For example, with A1 and B1 each containing a formula, this pair forms an indirect loop:

With A1 intended to be a value based on B1, enter =B1+10 in A1.

With B1 intended to be half of A1, enter =A1*0.5 in B1.

Under iteration, the values can converge toward A1 = 20 and B1 = 10, because those values satisfy both relationships. The result depends on the model, starting values, and iteration settings; this example illustrates a converging loop, not a reason to enable iteration for every warning. A loop can also fail to converge or produce a result that is sensitive to settings.

Iteration is a workbook calculation setting and can affect other circular formulas in the file, not just the cell currently under review. Document why the loop is intentional, test it with representative inputs, and check how results change when the starting values or settings change. On Mac, calculation settings are under Excel > Settings (or Preferences in some older builds) > Calculation; labels can differ slightly by release. If you only need to repair an accidental loop, leave iteration off and correct the formula instead.

Why the warning can keep coming back

If the error returns after you fix a cell, another formula may still complete the cycle. Check the Circular References list again and trace every listed path. A formula may also be reintroduced when someone pastes a range, restores an old version, or changes a named range. Reviewing recent edits and comparing the formulas in adjacent rows can reveal a single inconsistent reference.

Another source of confusion is calculation state. Manual calculation can make displayed values look unchanged even after formulas are edited, while iterative calculation can make an intentional loop appear to work. Neither behavior proves that the workbook is correct. Verify the formulas, confirm the workbook’s calculation mode, and recalculate before relying on the results. If another person uses the workbook, explain any intentional circular logic and its iteration settings so that the next edit does not turn it into an unnoticed error.

Frequently Asked Questions

Can I ignore a circular reference warning?
Only if the circular relationship is intentional and the workbook is set up to calculate it correctly. Otherwise, Excel may show an incomplete or unreliable result. Locate the loop and verify the calculation before using or sharing the workbook.

Why does Excel show a circular reference when no formula refers to itself?
The loop may pass through other cells or worksheets, or through a defined name. Trace precedents and inspect each referenced formula until the chain returns to the starting cell.

Will turning on iterative calculation fix the error?
It allows Excel to calculate circular formulas repeatedly; it does not remove the loop. Use it when the model deliberately depends on iteration, and set and test the iteration limits. For an accidental loop, correct the reference.

Why is the Circular References list empty?
The warning may no longer be active, the workbook may not have recalculated, or the relevant formula may be on another sheet. Recalculate, inspect the status bar, and review formulas and names around the cells that changed. Do not assume an empty menu proves the model is logically correct.

Does a circular reference mean my workbook is corrupted?
No. It usually means Excel found a dependency loop in one or more formulas. The workbook can remain usable after you correct the formula or deliberately configure a tested iterative model.

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.