# How to Find Circular References in Excel

> How to find, fix and remove a circular reference in Excel: the Error Checking menu, the status bar, trace arrows, iterative calculation and a free finder.

Page: https://formulaudit.com/guides/find-circular-references-excel

Updated 2026-11-12

## Quick answer

To find a circular reference in Excel, open the Formulas tab, click the arrow next to Error Checking, and choose Circular References. Excel lists a cell in the loop; click it to jump there. The status bar also shows 'Circular References' when a loop exists. To fix it, change one formula in the chain so it no longer points back to itself. To list every circular reference in a workbook at once, use the free Excel Circular Reference Finder.

To find a circular reference in Excel, open **Formulas**, click the arrow next to **Error Checking**, and choose **Circular References**. Excel lists a cell in the loop. Click it to jump there. To fix the reference, edit one formula in the chain so it no longer depends on its own result. If you need every circular reference in the workbook at once, open the file in the free [Excel Circular Reference Finder](https://formulaudit.com/free-tools/excel-circular-reference-finder).

## What is a circular reference in Excel?

A circular reference is a formula that depends on its own result. It comes in two forms:

- **Direct:** cell `C5` contains `=C5+1`. The formula refers to its own cell.
- **Indirect:** `C5` uses `D10`, `D10` uses `F3`, and `F3` uses `C5`. No single formula looks wrong, but the chain is a loop.

Excel cannot finish the calculation in one pass. It shows a warning when you enter the formula or open the file, and the cell usually displays `0`. In a financial model, a loop that returns a plausible number is the dangerous case, because nothing flags it as wrong.

## How to find a circular reference in Excel

1. Click the **Formulas** tab.
2. In the **Formula Auditing** group, click the arrow next to **Error Checking**.
3. Point to **Circular References**. Excel lists a cell address that is part of a loop.
4. Click the address. Excel selects that cell, on whichever sheet it is.
5. Look at the formula in the formula bar and note which cells it uses.

If **Circular References** is greyed out, Excel found no loop in the last calculation.

You can also check the **status bar** at the bottom of the window. It reads `Circular References` while a loop exists. When the loop is on the active sheet, it adds the cell address, for example `Circular References: B14`. When the loop is on another sheet, the address is missing, so use the Error Checking menu to find the sheet.

## Trace the loop with arrows

A single flagged cell does not show the whole loop. With the flagged cell selected:

1. Press **Alt+M, P** (Trace Precedents) to draw arrows from its inputs.
2. Press it again to go one level further back.
3. Follow any dotted arrow to another sheet by double-clicking it.
4. Keep going until an arrow leads back to the cell you started from. That is the loop.
5. Clear the arrows with **Alt+M, A, A** (Remove Arrows).

The [Trace Precedents guide](https://formulaudit.com/guides/trace-precedents-excel) covers multi-level tracing and cross-sheet arrows. The [Trace Dependents guide](https://formulaudit.com/guides/trace-dependents-excel) covers the forward direction. Both are useful here, because a loop runs in both directions.

## How to remove or fix a circular reference in Excel

Decide first whether the loop is intended.

**If it is a mistake**, break the chain at one point:

1. Pick the formula in the loop that should not depend on the others. This is often a total that was pointed at a range including itself, such as `=SUM(B2:B10)` entered in `B10`.
2. Edit it so it references the correct range or a separate input cell.
3. Press **Enter**, then recheck **Formulas, Error Checking, Circular References**.
4. Repeat until the item is greyed out.

Replacing one formula with a typed value also breaks the loop, but that hides the logic. Use it only as a temporary test. Press **Ctrl+Z** if an edit goes wrong.

**If it is intended**, turn on iterative calculation, described next.

## How to enable iterative calculation in Excel

Some models are circular on purpose. Interest calculated on the average debt balance is the usual example, because interest changes cash flow, which changes the closing balance, which changes interest.

1. Go to **File, Options, Formulas**. On Excel for Mac, go to **Excel, Preferences, Calculation**.
2. Under **Calculation options**, tick **Enable iterative calculation**.
3. Set **Maximum Iterations** (default 100) and **Maximum Change** (default 0.001).
4. Click **OK**.

The warning stops, and Excel repeats the calculation until the change between passes is below the maximum change. Two cautions:

- Excel takes this setting from the first workbook opened in a session. A file saved with iteration on can still warn on a machine where another workbook opened first.
- Iteration on a model that was never meant to be circular hides a real error. Turn it on only after you have confirmed the loop is deliberate.

For the Mac steps, the defaults and when a circular model is safe, see [How to Enable Iterative Calculation in Excel](https://formulaudit.com/guides/enable-iterative-calculation-excel).

## What native Excel does not do

- The Error Checking menu shows the cells from the last calculation. It does not draw the whole loop, and it does not give you one list of every loop.
- Fixing one loop can reveal the next, so you recheck after every edit.
- Tracing a loop that crosses many sheets means double-clicking dotted arrows to move between tabs.
- A loop through a defined name is hard to follow with arrows.

## Find every circular reference at once

The free [Excel Circular Reference Finder](https://formulaudit.com/free-tools/excel-circular-reference-finder) reads an `.xlsx`, `.xlsm` or `.xls` file in your browser and lists the circular references it finds across all sheets. The file is not uploaded to a server.

Formula Audit XL runs the same check inside Excel. The [Audit Checks](https://formulaudit.com/features/audit-checks) suite includes a Circular References check (Ctrl+Shift+C) that lists every loop in the workbook with the cells involved, and a click jumps to each one. To inspect a flagged cell, [Explore Precedents and Dependents](https://formulaudit.com/features/explore-precedents-dependents) opens a drill-down tree for it. Formula Audit XL is available as an Excel add-in for Windows and as an AppSource task pane for Windows, Mac, and Excel on the web.

## Common pitfalls

- **Fixing the wrong cell.** A loop involves at least two cells. If you change one formula and the warning stays, trace the whole chain before the next edit.
- **Turning on iteration to silence a warning.** It is the right answer for a deliberate loop. In any other model it hides a formula error.
- **Loops on hidden sheets.** The status bar gives no address when the loop is on another sheet. Use the Error Checking menu to see which sheet it is on.
- **Volatile functions in a loop.** If the chain includes `NOW()`, `RAND()`, or `OFFSET()`, the result recalculates on every edit and changes without warning.

## Related guides

- [How to Trace Precedents in Excel](https://formulaudit.com/guides/trace-precedents-excel)
- [How to Trace Dependents in Excel](https://formulaudit.com/guides/trace-dependents-excel)
- [Excel Trace Precedents and Dependents Shortcuts](https://formulaudit.com/guides/trace-precedents-dependents-shortcuts)
- [How to Find and Break External Links in Excel](https://formulaudit.com/guides/break-external-links-excel)
- [How to Audit a Financial Model in Excel](https://formulaudit.com/guides/audit-financial-model-excel)

Free tool: [Excel Circular Reference Finder](https://formulaudit.com/free-tools/excel-circular-reference-finder)

## The faster way

Run this check across your entire model with Formula Audit XL: [Audit Checks](https://formulaudit.com/features/audit-checks).

## FAQ

### What is a circular reference in Excel?

A circular reference is a formula that depends on its own result, either directly (=A1+1 typed in A1) or through a chain of other cells (A1 uses B1, B1 uses C1, C1 uses A1). Excel cannot finish the calculation in one pass, so it shows a warning and usually returns 0.

### How do I find a circular reference in Excel?

Go to Formulas, click the arrow next to Error Checking, and point to Circular References. Excel lists a cell in the loop. Click the address to go to it. The status bar also reads 'Circular References' while a loop exists, and shows a cell address when the loop is on the active sheet.

### How do I remove a circular reference in Excel?

Select a cell in the loop, find the formula that points back to the chain, and edit it so it references a different cell or a fixed input. Then recheck Formulas, Error Checking, Circular References. When the menu item is greyed out, no circular reference is left.

### How do I fix a circular reference in Excel?

First decide whether the loop is intended. If it is not, use Trace Precedents (Alt+M, P) from the flagged cell to see the chain, then break it at one point: point the formula at a non-circular input, or replace the formula with a value. Use Ctrl+Z if the edit makes things worse.

### How do I enable iterative calculation in Excel?

Go to File, Options, Formulas, and tick Enable iterative calculation under Calculation options. Excel defaults to 100 maximum iterations and a maximum change of 0.001. In Excel for Mac, the setting is under Excel, Preferences, Calculation. Enable it only when the loop is deliberate, such as interest on an average debt balance.

### Why does Excel show a circular reference warning when I open a file?

The workbook contains a loop, and iterative calculation is off in the session that opened it. You may have inherited the loop from someone else's model. Excel applies the iteration setting from the first workbook opened in the session, so the same file can warn on one machine and not on another.

### Why does Excel show only some circular references?

The Error Checking menu lists the cells Excel found during the last calculation, and the list changes as you fix loops. In a multi-sheet model, recheck the menu after each fix. A workbook-wide scan lists every loop in one pass.

## Related guides

- [Trace Precedents in Excel: Location & Shortcut](https://formulaudit.com/guides/trace-precedents-excel)
- [Trace Dependents in Excel: Shortcut & Other Sheets](https://formulaudit.com/guides/trace-dependents-excel)
- [Excel Trace Dependents & Precedents Shortcuts](https://formulaudit.com/guides/trace-precedents-dependents-shortcuts)
- [How to Break External Links in Excel](https://formulaudit.com/guides/break-external-links-excel)
- [How to Audit a Financial Model in Excel](https://formulaudit.com/guides/audit-financial-model-excel)
