What a circular reference is and why Excel flags it
A circular reference happens when a formula refers back to its own cell, either directly or through a chain of other cells. For example, if cell A1 contains the formula =A1+5, that is a direct circular reference — the cell is trying to calculate itself. More often, you will see indirect ones: A1 contains =B1, B1 contains =C1, and C1 contains =A1, creating a loop.
Excel cannot calculate a circular reference because there is no starting point — the formula would need to know its own answer before it can produce an answer. When you create one, Excel shows a warning dialog and will not complete the calculation. The cell displays 0 or the last calculated value, but the formula is broken.
Circular references are usually mistakes, but sometimes they are intentional — older spreadsheets occasionally used them with iteration turned on to solve certain types of problems. In modern Excel, there are almost always better ways to structure the data.
Key Takeaways
- Excel shows a warning dialog when you create a circular reference and will not calculate the formula until you fix it.
- Use the Circular Reference tool in the Formulas tab to find which cells are involved in the loop.
- Direct circular references (a cell referring to itself) are easier to spot; indirect ones (A refers to B, B refers to C, C refers to A) require tracing through multiple formulas.
- The fastest fix is usually to rewrite the formula so it refers to a different cell, or to move the calculation to a new cell entirely.
- If you intentionally use circular references with iteration enabled, document why so the next person who opens the file understands it.
How to locate a circular reference using Excel's built-in tool
When you first create a circular reference, Excel displays a dialog box that says "Circular Reference" and offers to help you find it. If you click OK without noting which cell you were in, you can still find it later using the Formulas tab.
Open the spreadsheet and go to the Formulas tab in the ribbon. Look for the Error Checking button (it looks like a small triangle with an exclamation mark). Click the dropdown arrow next to it and select Circular References. Excel will show you a list of all cells involved in circular reference loops. Click on any cell in the list and Excel will jump to it and highlight it.
If the list is empty but you know a circular reference exists, the formula may be in a different sheet. Check each sheet tab at the bottom of the window and repeat the process. Some circular references hide in sheets you do not actively use.
Tracing the path of an indirect circular reference
A direct circular reference (A1 = A1) is obvious once you look at the formula bar. An indirect one requires you to follow the chain. Start with one of the cells Excel identified and look at its formula. Note which other cells it refers to, then click on each of those cells and look at their formulas. Keep going until you find the cell that refers back to your starting point.
The Trace Precedents and Trace Dependents tools in the Formulas tab can speed this up. Trace Precedents shows you which cells feed into the current cell. Trace Dependents shows you which cells depend on the current cell. Use these arrows to visualize the chain and find where it loops back.
Write down the chain as you go — for example, "A1 refers to B2, B2 refers to C3, C3 refers to A1" — so you can see the full loop. This makes it much easier to decide which link to break.
Breaking the circular reference by rewriting the formula
Once you know which cells are in the loop, you need to break one link. The safest approach is to rewrite one of the formulas so it no longer refers to the cell that closes the loop.
For example, if A1 = B1 + 10, B1 = C1 + 5, and C1 = A1 + 2, you could change C1 to a fixed number or a reference to a different cell. You could also move the calculation to a new cell — create D1 = A1 + B1 + C1 and delete the circular formulas, then rebuild them to feed into D1 instead of into each other.
Before you delete anything, copy the formulas into a text editor or a comment so you have a record of what was there. Sometimes the circular reference was solving a real problem, and you will need to understand what it was trying to do before you can replace it with a better method.
When a circular reference is intentional (and how to document it)
Older spreadsheets sometimes use circular references with iteration enabled to solve problems that would otherwise require complex array formulas or helper columns. Iteration tells Excel to recalculate the sheet multiple times, allowing circular formulas to converge on an answer.
If you inherit a spreadsheet with intentional circular references, do not assume it is broken. Check the File menu, go to Options, then Formulas, and look for the "Enable iterative calculation" checkbox. If it is checked, the circular references are probably deliberate.
If you must keep a circular reference, add a comment to the cell explaining why it is there and what it does. Write something like "Intentional circular reference — solves for equilibrium price. Do not remove without rebuilding the model." This prevents the next person from "fixing" something that is actually working as designed.
Common places circular references hide
Circular references often appear in spreadsheets where someone has copied a formula down a column and accidentally included the header row, or where a summary row at the bottom refers back to cells that include it in their range. Check your SUM formulas especially — if a SUM formula is in the same range it is summing, that is a circular reference.
They also hide in spreadsheets with multiple sheets that reference each other. A formula in Sheet1 might refer to Sheet2, which refers back to Sheet1, and the loop is not obvious until you trace it. Use the Circular References tool to find these before they cause problems.
Conditional formatting rules and data validation formulas can also create circular references, though Excel usually catches these when ready. If you see a circular reference warning when you have not directly entered a formula, check your data validation rules and conditional formatting.
Preventing circular references in the first place
The best way to handle circular references is to avoid creating them. Think about the direction of your calculations before you write formulas. If A depends on B, then B should not depend on A. Draw a quick sketch of which cells feed into which if the spreadsheet is complex.
Use helper columns or separate calculation areas if you need to do multi-step math. Keep your summary rows and columns outside the ranges they summarize. If you find yourself writing a formula that refers to the cell it is in, stop and ask whether you should be calculating something else instead.
Test your formulas as you build them, especially if you are copying them down or across. A circular reference caught when ready is much easier to fix than one you discover weeks later in a spreadsheet someone else is using.
Frequently Asked Questions
Will Excel let me save a file with a circular reference in it?
Yes, Excel will save the file, but the circular reference will not calculate. The cell will show 0 or the last value it had before the circular reference was created. When you open the file again, Excel will warn you that it contains a circular reference. Fix it before you use the spreadsheet for real decisions.
What does it mean if the Circular References list is empty but I still see an error?
The error might not be a circular reference — it could be a #REF! error (a formula refers to a cell that no longer exists) or a #DIV/0! error (dividing by zero). Check the formula bar to see what the actual error is. The Circular References tool only finds loops, not other types of formula problems.
Can I use circular references if I turn on iteration?
Yes, but only if you understand what you are doing. Iteration makes Excel recalculate the sheet multiple times until the values stabilize. This works for certain financial models and engineering calculations, but it is slow and confusing for most people. Document it clearly if you use it.
If I have a circular reference in one sheet, does it affect other sheets?
Only if those other sheets reference the cells involved in the loop. A circular reference in Sheet1 will not break formulas in Sheet2 unless Sheet2 is pulling data from the broken cells in Sheet1. Check your cross-sheet references if you see unexpected errors.
What if I need to reference a cell in a formula that is in that same cell?
You cannot do that directly, but you can restructure the problem. If you need the result of a calculation to feed back into itself (like a running total), use a different cell for each step, or use a helper column. If you need to reference the previous value of a cell, store it in a separate cell and update that cell manually or with a macro.