How to find and fix a circular reference in Excel
A circular reference happens when a formula refers to its own cell, directly or through other cells. Excel cannot calculate it, so it shows a warning and usually returns 0. To fix it, open Formulas > Error Checking > Circular References, jump to the cell it lists, and change the formula so that it no longer depends on itself.
This guide shows how to find the loop, the five situations that cause most of them and how to fix each one, and the rare case where a loop is intended. Practice the underlying skills in the lessons linked at the end.
1. What a circular reference is
A formula such as =A1+B1 typed into A1 asks Excel for a value that needs A1 first, and A1 needs the formula. There is no starting point, so Excel stops. A loop can also be indirect: A1 uses B1, B1 uses C1, and C1 uses A1. No single formula looks wrong, but together they chase each other in a circle.
When you enter such a formula, Excel shows a warning. After you close it, the cell usually displays 0, and the status bar says Circular References followed by a cell address.
2. Find the cell
Go to Formulas > Error Checking, point to Circular References and pick a cell from the list. Excel jumps to it. Click Formulas > Show Formulas, or press Ctrl+` (the key above Tab), to see every formula as text, which makes a self-reference easier to spot. Use Formulas > Trace Precedents to draw arrows to the cells the formula reads: an arrow that comes back to the same cell is the loop.
The status bar only names one cell, and it may be a cell in the middle of the loop. Fix that cell, then check the list again, because there can be more than one loop in a workbook.
3. Fix a total that includes itself
The most common cause: a total placed inside the range it adds, such as =SUM(B2:B10) typed in B10. The formula adds the cell it lives in. Change the range to stop above the total, =SUM(B2:B9), or move the total outside the data. Inserting a row inside a range can also stretch it to include a total row, so check the range after you insert.
4. Fix a percentage or a price that feeds itself
Two cells that each depend on the other cause an indirect loop. For example, a price with tax in C2 that uses a discount in D2, while D2 is calculated as a percentage of C2. Decide which value is the input and which is the result, and calculate the result from the input only. Here, calculate the discount from the price before tax, and then the final price from both.
Write down the order of the calculation on paper first: inputs, then each result in turn. If the order needs a result before it exists, the model has a real circular logic problem and you must restructure it, not just edit a formula.
5. Fix a running total that refers to itself
A running total in C2 written as =C2+B2 refers to itself. The correct form adds the previous total: in C3, =C2+B3, and in C2 just =B2. Copy it down. Anchor the first cell of a range with a dollar sign when you sum from the top: =SUM($B$2:B3). This lets you copy the formula without moving the start.
6. Fix a formula that was copied or moved
Sometimes a good formula becomes circular after you copy it, because a relative reference now points at its own cell. Check the formula in the new place, and use a dollar sign on the part that should not move. A lookup table pasted over the cells it reads is another source. Also look for a reference in another sheet or workbook that points back to the original sheet.
7. When a loop is on purpose
A few models need a loop, such as interest calculated on an average balance that includes the interest. In that case go to File > Options > Formulas and tick Enable iterative calculation. Set the maximum iterations (100 is the default) and the maximum change (0.001). Excel then recalculates the loop until the change is smaller than that limit.
Turn it on only when you really need it. With iteration on, Excel no longer warns about accidental loops, so a typing mistake can hide for a long time. Add a note next to the model saying that iteration is required.
Common mistakes
Turning on iterative calculation to silence the warning. The warning is gone, but the formula still depends on itself and gives a wrong number.
Fix: Find the cell, correct the formula, and keep iteration off unless the model needs it.
Ignoring the 0 result. A circular formula usually shows 0, which looks like a real value.
Fix: Treat a surprising 0 as a sign to check the status bar for Circular References.
Fixing only the cell Excel names. The status bar names one cell, but the loop may pass through others.
Fix: After each fix, open Error Checking again until the Circular References entry is gone.
Frequently asked questions
Why does Excel show 0 for a circular reference?
Excel cannot finish the calculation, so it keeps the last value it had, which is 0 for a new formula. The cell is not correct, even though it holds a number.
Why is there no circular reference warning when I open the file?
The workbook may have iterative calculation turned on, or the warning may be off in Excel options. Check File > Options > Formulas.
Can {fn:IF} stop a circular reference?
Not by itself. Excel still sees the reference in the formula, even when the IF would not use it. Remove the reference, or use a calculation order that does not need it.
Related functions
- SUMSUM adds up numbers, cells and whole ranges in one formula.
- IFIF checks a condition and returns one value if it's true and another if it's false.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.
More on circular references
The guide shows how to find a loop and fix the common causes. This piece adds how to read the arrows, how a loop hides across sheets, and how to build a model that needs iteration safely.
Read the trace arrows
Select the cell Excel names and choose Formulas > Trace Precedents. Blue arrows point from every cell the formula reads. Click it again to go one level further back. In a loop the arrows lead you around and return to the cell you started from. Trace Dependents works the other way, showing which cells use the selected one, and it helps when you do not know which formula to change first.
Remove the arrows with Remove Arrows when you are done, because they stay on the sheet until you clear them.
Loops that hide across sheets
A loop does not have to stay on one sheet. A summary sheet can read a detail sheet that, in a corner you rarely open, reads the summary back. Error Checking lists the cell, and the sheet name is shown in the formula bar. When the trace arrows reach another sheet they turn into a dashed line with a small sheet icon. Double-click the dashed arrow to jump to the other cell.
=SUM(Detail!C2:C20)
=Summary!B2*0.1Using iteration safely
If the model really needs a loop, keep it small. Put the looping formula in one clearly labeled cell, and give it a sensible starting value so the first pass does not produce an error. Wrap the formula with IFERROR, because an error inside a loop passes around and never clears. Compare the result with a calculation done by hand once, to be sure the loop settles on the right number.
Practice the references that avoid most loops in [the cell references lesson](/learn/cell-references), and the error tools in [the errors lesson](/learn/errors-iferror).
Habits that avoid loops
Keep inputs, calculations and results in separate areas, and let information flow in one direction only, from inputs to results. Keep totals outside the ranges they add. When you insert rows, check the formulas that sum across the insertion point. Most loops come from a total that was moved or a range that grew, and a clear layout makes both easy to see.