Circular reference in Excel
A formula depends on its own result, directly or through other cells, so Excel cannot finish the calculation.
What Circular reference means
A circular reference is a formula that depends on its own result. It is not an error value like #N/A: Excel raises a warning when you enter the formula, and the cell usually shows 0.
The loop can be direct, such as a total whose range includes the total itself, or indirect, running through several cells. In both cases Excel would need the answer to calculate the answer, so it stops.
You find it from the status bar. After the warning is dismissed, the bottom left of the window shows Circular References followed by one cell address, which is the first cell Excel found in the loop. That cell is where to start reading formulas.
Causes of Circular reference and how to fix each
1. A total that includes its own cell
The most common version is a SUM range that was stretched one cell too far, so the total sits inside the range it is adding. Excel would need the total to compute the total, so it stops, shows a warning, and displays 0 in the cell.
| A | |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 0 |
Fix. Shrink the range so it stops above the formula: =SUM(A1:A2). If you are adding rows, insert them between the first and last row of the range rather than below the total.
| A | |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
The total now adds 10 and 20.
2. Two cells that depend on each other
A chain can loop through several cells. If the formula in A1 uses B1 and the formula in B1 uses A1, neither can be calculated first. This happens when a model is edited by several people, or when a copied formula points back at the cell it came from.
| A | B | |
|---|---|---|
| 1 | 0 | 0 |
Fix. Read the formulas in both cells and decide which one should hold an input and which one should hold the calculation. The loop is broken when one of them refers only to data that does not depend on the other.
3. A running balance pointing at the wrong row
A balance column adds each deposit to the balance above it, as in =A2+B3 in A3. If the reference to the previous balance slips down one row, it becomes =A3+B3 and the cell now depends on itself.
| A | B | |
|---|---|---|
| 1 | Balance | Deposit |
| 2 | 500 | |
| 3 | 0 | 50 |
Fix. Click the cell and check which cell each reference points at. The first row should hold the opening balance as a number, and each later row should refer to the row above it, so A3 should read =A2+B3.
| A | B | |
|---|---|---|
| 1 | Balance | Deposit |
| 2 | 500 | |
| 3 | 550 | 50 |
A2 holds 500 and B3 holds 50, so the new balance is 550.
Trapping Circular reference in a formula
No worksheet function traps a circular reference, since the loop stops the calculation before IFERROR can run. The only way to leave a loop in place is to turn on iterative calculation in File, Options, Formulas, which makes Excel repeat the loop a set number of times.
Iteration is for the rare model that needs it, such as an interest calculation where the interest depends on the average balance. Leave it off otherwise, because with it on Excel stops warning you about accidental loops.
Circular reference on Excel job tests
A workbook handed over for repair may carry a circular reference you are expected to find. Excel shows the warning on open and the status bar names a cell. If the status bar is blank, go to Formulas, Error Checking, Circular References, which lists every cell in the loop.
The most frequent task version is a total that sits inside its own range. Shrinking the range to stop above the total is the whole answer.
Related guides
Circular reference questions
- What is a circular reference in Excel?
- A formula that refers to its own cell, directly or through a chain of other cells. Excel cannot calculate it and shows a warning.
- How do I find a circular reference?
- Look at the status bar, where Excel shows
Circular Referencesand a cell address. Or use Formulas, Error Checking, Circular References for the full list. - Why does the cell show 0?
- Excel cannot complete the calculation, so it shows 0 or the last value it had. The 0 is not a result.
- Should I turn on iterative calculation?
- Only for a model that is designed to loop. Otherwise it hides accidental loops, because Excel stops warning you.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample