What a circular reference is and why Excel warns you
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. If A1 contains =B1, B1 contains =C1, and C1 contains =A1, that is an indirect circular reference — the chain loops back to where it started.
Excel cannot calculate a circular reference because it has no final answer to work toward. The formula would need to know its own result before it can calculate its own result. When you create one, Excel shows a warning dialog and will not let the formula calculate properly. The cell displays 0 or the last calculated value, but the formula is broken.
Most circular references are mistakes — a typo in a formula, or a formula that accidentally includes the cell it sits in. Some people deliberately create them and tell Excel to iterate (recalculate repeatedly) until the values stabilize, but that is rare and risky. The first step is always to find where the loop is.
Key Takeaways
- Excel shows a warning dialog when you create a circular reference and will not calculate the formula until you fix it.
- The Circular Reference tool in the Formulas tab shows you which cells are part of the loop, one step at a time.
- Indirect circular references (where the loop goes through multiple cells) are harder to spot but the same tool finds them.
- Most circular references are typos — check that your formula does not include its own cell address, and that cell references point to the right columns and rows.
Using the Circular Reference tool in the Formulas tab
Excel has a built-in tool that walks you through each cell involved in the circular reference. Open the spreadsheet with the circular reference warning. Click the Formulas tab at the top of the ribbon. In the Formula Auditing group, click Circular References — it is a dropdown menu.
The dropdown shows a list of cells that are part of circular reference loops. Click on the first cell in the list. Excel will jump to that cell and highlight it. Look at the formula bar to see what formula is in that cell. Then click Circular References again and select the next cell in the list. Excel will show you the next cell in the chain, and you can see which cell it references. Keep clicking through the list until you see where the loop closes — where a cell references back to one you have already seen.
This method works for both direct and indirect circular references. The tool shows you the path the formula takes, so you can see exactly where the loop is and what caused it.
Checking formulas for the most common mistakes
Once you know which cells are in the loop, look at each formula and check for these common errors. The most frequent mistake is including the cell's own address in its formula. For example, if you are in cell D5 and the formula is =D5+C5, that is a direct circular reference. The fix is to remove the cell's own address from the formula.
The second common mistake is a typo in a cell reference. If you meant to reference C5 but typed D5 by accident, and D5 references back to your original cell, you have created an indirect loop. Check that each cell reference in the formula points to the column and row you intended. Use the arrow keys or click on the cell reference in the formula bar to verify which cell it actually points to.
The third mistake is copying a formula down a column and accidentally including the row you are copying into. For example, if you copy a formula from D4 down to D5, and the formula references D4, then D5 now references D4 and D4 references D5 — a loop. The fix is to adjust the formula so it references the row above, not the current row.
Tracing cell references with the auditing tools
If the Circular References list is long or the loop is hard to follow, use the Trace Precedents tool to see which cells feed into the formula. Click on a cell in the loop. In the Formulas tab, click Trace Precedents. Excel draws blue arrows from the cell to every cell it references. Click Trace Precedents again to go one level deeper and see what those cells reference.
You can also use Trace Dependents to see which cells reference the current cell. This shows you what depends on the cell you are looking at. Together, these tools let you follow the chain forward and backward until you see where it loops.
When you are done tracing, click Remove Arrows in the Formula Auditing group to clear the blue lines from the screen.
Fixing the circular reference once you find it
Once you know which cell is causing the loop, edit the formula to remove the circular reference. Click on the cell and look at the formula bar. Delete the part of the formula that references the cell itself, or change the cell reference to point to a different cell. Then press Enter.
If the formula is supposed to reference the cell above it, make sure you are using a relative reference (like =D4 when you are in D5) so that when you copy the formula down, each row references the row above it. If you use an absolute reference (like =$D$5), the formula will always point to the same cell no matter where you copy it, which can create a loop if you copy it to the wrong place.
After you fix the formula, Excel will recalculate immediately and the warning will disappear. If the Circular References list still shows cells, repeat the process for the next cell in the loop.
When you intentionally want circular references (rare)
Some advanced spreadsheets use circular references on purpose, with Excel set to recalculate the formula repeatedly until the values stop changing. This is called iteration. It is used for certain financial models and goal-seek calculations, but it is risky because the formula might never stabilize or might give different results each time.
If you need to use iteration, you must turn it on manually. Click File, then Options, then Formulas. Check the box for Enable iterative calculation. You can also set the maximum number of times Excel will recalculate (the default is 100) and how close the values need to be to stop (the default is 0.001). Only do this if you understand what the formula is supposed to do and why it needs to loop.
Preventing circular references in the future
The best way to handle circular references is to avoid creating them. When you write a formula, think about what cells it needs to reference and make sure none of them reference back to the cell you are in. If you are copying a formula down a column, check that the formula references the row above or a fixed row, not the current row or a row below.
Use meaningful cell names instead of cell addresses when possible. If you name a range of cells, it is easier to see at a glance whether a formula is referencing the right thing. For example, =SUM(Sales) is clearer than =SUM(D2:D100) and less likely to accidentally include the wrong cell.
If you inherit a spreadsheet with circular references already in it, use the Circular References tool immediately to find them and fix them before you add more formulas. A spreadsheet with unresolved circular references will give you wrong answers and make it hard to debug other problems later.
Frequently Asked Questions
Does Excel automatically fix circular references?
No. Excel shows you a warning and stops the formula from calculating, but it does not fix the problem itself. You have to find the circular reference and edit the formula to remove it. Excel will not let you save the file with unresolved circular references in some cases, depending on your version and settings.
Can I have more than one circular reference in the same spreadsheet?
Yes. A spreadsheet can have multiple separate loops. The Circular References dropdown will list all of them. You have to fix each one individually. Start with the first cell in the list, trace it to find the loop, fix the formula, then move to the next cell in the list.
What if the Circular References list is empty but I still see a warning?
This can happen if you have already fixed the circular reference but Excel has not recalculated yet. Press Ctrl+Shift+F9 to force a full recalculation of the entire spreadsheet. If the warning still appears, close the file without saving and reopen it to see if the problem persists.
Can I undo a circular reference if I created it by accident?
Yes. Press Ctrl+Z to undo the last action. Excel will restore the formula to what it was before you created the circular reference. If you have already closed the file, you cannot undo it — you will have to fix the formula manually.
Why does my cell show 0 instead of an error message?
Excel displays 0 as a placeholder when a circular reference cannot be calculated. This is the default behavior. Some versions of Excel will show the last calculated value instead if the formula was working before the circular reference was created. Either way, the formula is not calculating correctly and needs to be fixed.