← Back to Error Directory

How to Fix Circular Reference Error in Excel & Google Sheets

Not a "#" error, but arguably the most disruptive warning in Excel — here's how to trace it, understand it, and decide whether to fix it or embrace it.

Table of Contents

1. What a Circular Reference Actually Is

A circular reference happens when a formula's calculation chain eventually loops back and depends on its own result. Excel's calculation engine works by building a dependency tree — each formula waits for its inputs to be resolved before it can calculate. A circular reference breaks that model entirely: cell A depends on cell B, but cell B (directly or through a longer chain) depends back on cell A. There is no valid order in which to calculate them, because each one needs the other to finish first.

This is different from every "#" error on this site — Excel doesn't return a value like #REF! or #NUM! in the cell. Instead, it shows a warning dialog the moment you create the loop, and displays a small circular arrow icon and a status bar warning until it's resolved.

2. Direct vs Indirect Circular References

Direct circular reference

The simplest form — a cell's formula refers to itself:

Cell A1: =A1+10        → Direct circular reference

Indirect circular reference

More common in real spreadsheets, and much harder to spot — the loop passes through several other cells before returning to the original:

Cell A1: =B1*2
Cell B1: =C1+5
Cell C1: =A1-3          → C1 depends on A1, which depends on B1, which depends on C1

Indirect loops like this are the ones that cause the most confusion, because the cell Excel highlights as "the" circular reference may not be the one where the actual logic mistake lives — you often need to trace two or three cells backward to find where the loop was introduced.

3. Interactive Demo: Trace the Loop

Build a small dependency chain below and see whether it resolves or loops.

The default example (A1=B1*2, B1=C1+5, C1=10) resolves cleanly. Change C1 to "A1-3" to introduce a real loop and see it detected.

4. Finding the Exact Cell Causing It

  1. Go to the Formulas tab.
  2. Click the small dropdown arrow next to Error Checking in the Formula Auditing group.
  3. Hover over Circular References — Excel lists every cell address currently involved in a loop.
  4. Click any listed cell address to jump directly to it.
  5. Use Trace Precedents and Trace Dependents (also in Formula Auditing) on that cell to visually map the arrows showing exactly which cells feed into it and which cells it feeds into — the loop becomes visible as an arrow that eventually points back to your starting cell.

5. Fixing Accidental Circular References

The vast majority of circular references are simple mistakes, most commonly from one of these patterns:

The fix in all these cases is the same: correct the range or reference so it excludes the cell containing the formula itself.

6. Intentional Circular References (Interest Models)

Not every circular reference is a mistake. Certain financial models genuinely require a value to depend on itself — the classic example is a loan schedule where interest expense depends on the average cash balance, but the cash balance itself depends on how much interest was paid. This is a legitimate "interest-on-interest" or "circularity switch" pattern used deliberately in corporate finance modeling, and it requires iterative calculation to resolve rather than being an error to eliminate.

7. Enabling Iterative Calculation Safely

If your circular reference is intentional:

  1. Go to File → Options → Formulas.
  2. Under Calculation options, check Enable iterative calculation.
  3. Set Maximum Iterations (default 100) and Maximum Change (default 0.001) — Excel will recalculate the loop repeatedly until the change between iterations falls below this threshold, then stop.

Important caution: enabling this setting applies to the entire workbook, not just the intentional loop. Any accidental circular reference elsewhere in the same file will now silently "resolve" to some converged number instead of warning you — which can hide a genuine mistake. Many experienced modelers add a dedicated "circularity switch" cell (a simple TRUE/FALSE toggle that breaks the loop with an IF statement) so the intentional circularity can be turned off instantly for auditing, without relying on the iterative calculation setting at all.

8. Hidden Circular References via Named Ranges

Circular references can hide inside named ranges, especially in large models where a name like TotalRevenue is used in dozens of formulas. If that name's underlying range definition is later edited to include a cell that itself uses TotalRevenue, the loop is easy to miss because nothing in the visible formula looks self-referential. Use Name Manager (Formulas tab) to review exactly what range each name points to whenever you're debugging an unexplained circular warning.

9. Google Sheets Differences

Google Sheets handles circular references similarly but with one key difference: it does not show an interruptive warning dialog by default. Instead, cells involved in the loop simply display a "Circular dependency detected" error text directly in the cell. Google Sheets also has its own iterative calculation setting under File → Settings → Calculation, functioning the same way as Excel's, with configurable max iterations and convergence threshold.

10. Best Practices

11. Frequently Asked Questions

What is a circular reference error in Excel?

A circular reference occurs when a formula in a cell refers back to itself, either directly or through a chain of other cells, creating a loop that Excel cannot resolve to a single stable value without iterative calculation.

How do I find which cell is causing a circular reference?

Go to Formulas tab, click the dropdown arrow next to Error Checking, and select Circular References. Excel will list the exact cell address of the reference causing the loop, which you can click to navigate directly to it.

Should I enable iterative calculation to fix a circular reference?

Only if the circular reference is intentional, such as in interest-on-interest financial models or goal-seeking calculations. If the circular reference is accidental, enabling iterative calculation just masks the problem by producing a converged but likely incorrect number instead of fixing the actual formula mistake.

Why does Excel sometimes let me create a circular reference without warning?

If iterative calculation is already enabled in the workbook (from a previous intentional use), Excel will not show the warning dialog for new circular references either — it will simply calculate them iteratively, which is why unexpected circular references can slip through unnoticed in workbooks that already use intentional circularity elsewhere.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.