What Is a Circular Reference?
A circular reference occurs when a formula in cell A refers to cell B, and cell B refers back to cell A — directly or through a chain of other cells. Because A depends on B and B depends on A, Excel cannot calculate a definitive answer. It would need to calculate A to know B, but it needs B to calculate A — an impossible loop.
When Excel detects a circular reference, it either:
- Shows a warning message: "There are one or more circular references where a formula refers to its own cell either directly or indirectly"
- Returns 0 for the affected cells (the most dangerous behaviour, because the cells look calculated)
- Or, if Iterative Calculation is enabled, calculates the formula a fixed number of times and shows whatever result it reaches after those iterations
The Most Common Cause: SUM Range Includes the Formula Cell
By far the most common circular reference is a SUM formula that accidentally includes its own cell in the range. For example:
=SUM(A1:A10)If this formula is in cell A10, it is trying to sum A1 through A10 — including itself. Excel cannot calculate this because A10's value depends on A10's value.
This usually happens when someone types the SUM at the bottom of a column and drags the range one row too far, or when rows are added to a table and the range is not adjusted correctly.
How to Find Circular References in Excel
Method 1: Built-in Error Checking (easiest)
- Go to the Formulas tab on the ribbon
- Click Error Checking (the dropdown arrow next to it)
- Hover over Circular References
- A submenu lists every cell with a circular reference in the current sheet
- Click any cell in the list to navigate directly to it
Important: this only shows circular references on the current sheet. If you have multiple sheets, you need to check each one individually.
Method 2: Status bar indicator
When any sheet in the workbook has a circular reference, Excel displays "Circular References: [cell address]" in the bottom status bar. This is visible at all times while the circular reference exists. Click it to navigate to the affected cell.
Method 3: Trace Dependents
For complex circular reference chains, use Formulas → Trace Dependents and Formulas → Trace Precedents to draw arrows showing which cells feed into which. Follow the arrows until you find the loop.
How to Fix a Circular Reference
Fix 1: Adjust the SUM range
If your SUM range accidentally includes the formula cell, simply adjust the end of the range to exclude it. If =SUM(A1:A10) is in A10, change it to =SUM(A1:A9).
Fix 2: Move the formula to a different cell
If the formula logically belongs in a cell that is also part of the data range, consider moving the total to a different row or column that is clearly outside the data area.
Fix 3: Use a helper column
If the circular logic is genuinely needed (rare, but happens in financial models with interlinked calculations), restructure the calculation using a separate helper column to break the dependency chain.
Fix 4: Intentional circular references with Iterative Calculation
Some advanced financial models intentionally use circular references (e.g., for interest calculations that depend on the ending balance, which depends on the interest). These require Iterative Calculation to be enabled: File → Options → Formulas → Enable Iterative Calculation. Set the Maximum Iterations to a number that produces stable results (typically 100–1000). Only do this if you understand the model logic — unintended circular references with iterative calculation enabled will silently produce wrong values.
Circular References That Show 0 Without Warning
The most dangerous scenario: a circular reference that exists but never shows a warning because Iterative Calculation is already turned on. The formula returns 0 (or whatever value the iterative loop converges to) and the workbook appears to calculate normally. If you inherited a workbook and your totals look wrong, check File → Options → Formulas for the Iterative Calculation setting.