Remove a Circular Reference Error in Excel Quickly

To remove a circular reference in Excel, select Formulas > Error Checking > Circular References, open the listed cell, and edit its formula so it no longer refers to itself, directly or through other cells. Repeat for any remaining circular references; move totals or other calculations outside the ranges they summarize when that is the cause.

What a circular reference means

A formula has a circular reference when its calculation depends on its own result. The dependency can be direct, as when a formula in A1 refers to A1, or indirect, as when A1 refers to B1 and B1 refers back to A1. Excel cannot calculate that relationship as a simple one-way sequence because it would need a value before it could produce that same value.

For example, if a total formula is entered in B10 and sums B2 through B10, the formula includes its own cell. The intended calculation is probably to add the values above the total, not to include the total itself. A circular reference can also arise across several sheets, so the cause is not always obvious from the cell that Excel identifies.

Excel may display a circular-reference warning when a workbook is opened or a formula is entered. Depending on calculation settings and the workbook’s state, a result may appear as zero, an earlier value, or another value that is not reliable. Treat the warning as a sign to inspect the formula relationships, rather than assuming the displayed number is correct.

Find the cell causing the error

In Excel for Microsoft 365 on Windows, start with Formulas > Error Checking > Circular References. The menu can list a cell address; select an address to go to that cell. Examine the formula bar and its referenced cells. If there are several problems, correct one, then check the menu again. A workbook can contain more than one circular loop, and fixing one does not necessarily fix the others.

Excel may also show a Circular References message and a cell address on the status bar at the bottom of the window. Select the address if it is available. The status bar and Error Checking menu are useful starting points, but they may not explain the intended calculation, especially when the dependency crosses worksheets.

To follow a complicated relationship, select a formula cell and use Formulas > Trace Precedents to see cells that feed into it, or Formulas > Trace Dependents to see cells that rely on it. Follow the arrows through the relevant cells and sheets until you find a path that returns to the starting cell. Remove the arrows afterward with Formulas > Remove Arrows. These tools help reveal dependencies; they do not decide which formula is logically correct.

If the location is unclear, inspect formulas and ranges manually. On Windows, Ctrl+` toggles the display of formulas on the worksheet; press the same shortcut again to return to values. On a Mac, use Formulas > Show Formulas if that shortcut is unavailable or differs with the keyboard layout. Also check formulas on other sheets, named ranges, and totals that may include their own output cell.

Correct the formula without changing the intended result

The right repair depends on what the formula is supposed to calculate. Do not simply delete a formula because Excel reports it; first identify the intended inputs and output. Common fixes include moving a total to a separate row, shortening a range so it excludes the formula cell, or correcting a cell reference that points to the wrong location.

Move a total below its data. If values are in B2:B10, place the total in B11 rather than in a cell included in the sum.
Exclude the result cell from its own range. Check both the first and last cells in a range, as well as any inserted rows that may have changed it.
Correct an accidental reference. A copied formula may point to the wrong row or column. Check whether relative and absolute references are appropriate before filling formulas down or across.
Separate input and output areas. If a formula is meant to calculate a value from user-entered inputs, keep those inputs in different cells from the formula’s result.

For a concrete example, assume B2:B10 contains the numbers 10, 20, 30, 40, 50, 60, 70, 80, and 90, and B11 is the intended total cell. With those values in B2:B10, enter this formula in B11:

=SUM(B2:B10)

The result is 450. If the same formula were placed in B10, it would include the formula cell in the range and create a circular reference. Moving it to B11 preserves the intended sum without asking Excel to calculate the result using itself.

Common causes in everyday workbooks

Totals placed inside their own source ranges are among the easiest mistakes to spot, but circular references often come from less obvious edits. A copied formula may have shifted to a self-reference, or a range may have expanded when rows were inserted. A formula on one sheet can also depend on a result on another sheet that eventually depends back on the first.

Look especially at these situations:

Running totals: A running-total formula may refer to the previous row, but an incorrectly anchored range can include the current result cell.
Percentage or commission calculations: A value might be calculated from a total that itself includes that value. Clarify whether the calculation should use a base amount, a subtotal, or a final total.
Subtotal rows: A total can accidentally include a subtotal or grand-total row that depends on the total being calculated.
Cross-sheet models: For example, a summary sheet can pull a value from a detail sheet while a detail formula also pulls an adjustment from the summary.
Named ranges and tables: A name or structured reference may include more cells than its label suggests. Check the range definition and the cells the formula actually covers.

A useful diagnostic question is: “What exact cells should be inputs to this result?” Compare that intended list with the formula’s references. This is more reliable than repeatedly changing references until the warning disappears, which can remove the circularity while producing a wrong answer.

When iterative calculation is appropriate

Some models intentionally use circular calculations. In that case, Excel can recalculate the formula repeatedly, using a previous result in each pass, until it reaches a stopping condition. This is called iterative calculation. It can be appropriate for a deliberately designed model where the relationship is understood and the result is expected to converge.

For example, suppose B2 is intentionally defined as 10% of its own value plus 100. The cell’s equation is x = 0.1x + 100, whose stable solution is about 111.11. With iterative calculation enabled, Excel can approach that value over repeated passes. This is an illustration of a mathematical relationship, not a recommendation to enable iteration for an unexplained warning.

To enable the setting in Excel for Microsoft 365 on Windows, go to File > Options > Formulas, select Enable iterative calculation, and review Maximum Iterations and Maximum Change. More iterations may allow additional passes; Maximum Change sets how small the change between results must become before Excel stops. The appropriate settings depend on the model. Too few iterations or a poorly chosen change threshold can leave an imprecise result, and a circular formula that does not converge may not produce a useful answer.

On Excel for Mac, calculation preferences are under Excel > Preferences > Calculation; the exact presentation can vary by release. If you are using Excel for the web and cannot find the calculation controls you need, open the workbook in the desktop app to review them. Iterative calculation is a workbook calculation setting, so coordinate with others who use the file and confirm that its results make sense after recalculation.

Do not turn on iteration just to silence the warning. If the loop was accidental, iteration can conceal the design error and give numbers that look plausible but are not the intended answer. First establish why the loop exists, document the calculation logic, and test the output against a known example.

Check the repair and prevent the warning from returning

After changing a formula, revisit Formulas > Error Checking > Circular References and confirm that no unintended references remain. Recalculate the workbook if needed, then compare the result with a hand calculation or a small test case. Also inspect dependent totals and charts: removing a loop can change a result, and downstream calculations may need review.

For a workbook that is shared or used repeatedly, reduce the chance of a recurrence by keeping inputs, intermediate calculations, and final outputs in clearly separated areas. Label totals, avoid placing them inside their source ranges, and review formula ranges after inserting rows or copying formulas. If the circular relationship is intentional, explain why in a cell note or workbook documentation and record the iteration settings used.

When a warning appears only in a particular workbook, check whether it contains an intentional iterative model before changing application settings. When a warning appears after a recent edit, undoing that edit may help identify the cause, but it is not a substitute for correcting the formula. Save a copy before making broad changes to a complex model, especially when its formulas feed reports or decisions.

Frequently Asked Questions

How do I find all circular references in Excel?
Use Formulas > Error Checking > Circular References and inspect the listed cell addresses. Fix a loop and check the menu again, because additional circular references may remain. Trace Precedents and Trace Dependents can help when the loop spans multiple cells or sheets.

Why does Excel show a circular reference when my formula looks correct?
The formula may depend on itself indirectly through another cell, sheet, named range, or subtotal. Follow the referenced cells until you find a path back to the original formula. Also verify that the formula range has not expanded to include its own cell.

Can I ignore a circular reference warning?
Only if the circular relationship is intentional and the workbook is designed to use iterative calculation. Otherwise, the displayed result may not be reliable. Confirm the calculation logic and settings rather than ignoring the warning because a number appears in the cell.

Does enabling iterative calculation remove the circular reference?
No. It allows Excel to recalculate a circular relationship repeatedly; it does not remove the dependency. Use it for deliberate models that converge, not as a general fix for accidental formula errors.

Why can’t I see the Circular References menu item?
The menu may not show a listed cell when Excel does not currently detect a circular reference, or the issue may have changed after recalculation. Check the status bar, confirm the workbook is calculating, and inspect recent formula edits. For calculation controls or more detailed investigation, use the desktop Excel app.

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.