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 — the cell is trying to use its own value to calculate itself. If A1 contains =B1, B1 contains =C1, and C1 contains =A1, that is an indirect circular reference — the chain loops back.
Excel cannot calculate a circular reference because there is no starting point. The program needs a value to begin with, but the formula keeps pointing back to itself. When you create or open a file with a circular reference, Excel displays a warning dialog telling you one exists, but it does not tell you where. The cell will show 0 or the last calculated value, but the result is not reliable.
Circular references are almost always mistakes. They happen when you copy a formula to the wrong place, when you edit a formula and accidentally include the cell you are editing, or when you build a chain of lookups that accidentally loops back. Occasionally someone creates a circular reference on purpose to model a repeating calculation, but that requires turning on iterative calculation in Excel settings, which is rare and usually a sign the spreadsheet should be redesigned.
Key Takeaways
- Excel shows a warning dialog when you open or create a file with a circular reference, but you have to use the Error Checking tool to find which cell contains it.
- The fastest way to locate a circular reference is to go to Formulas > Error Checking > Circular References, which lists every cell involved in the loop.
- Once you find the cell, trace the formula backwards by clicking on the cells it references to see where the loop closes.
- Most circular references are fixed by changing one formula to reference a different cell or by deleting a formula that should not be there.
- If you cannot find the circular reference using Error Checking, it may be on a different sheet — check all sheets in the workbook.
Using the Error Checking tool to find the cell
When Excel warns you about a circular reference, click OK to close the dialog. Then go to the Formulas tab at the top of the ribbon. In the Formula Auditing group, click Error Checking and select Circular References from the dropdown menu.
Excel will list every cell that is part of a circular reference chain. If you have only one loop, you will see a short list — often just two or three cells. 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 the cell is calculating. This is your starting point.
If the list is long or you have multiple separate circular references, start with the first one. Fix it, then run Error Checking again. The list will shrink as you resolve each loop.
Tracing the loop by following cell references
Once you have clicked on a cell from the Circular References list, look at its formula in the formula bar. The formula will reference one or more other cells — for example, =B5+C5 or =SUM(D1:D10). Click on each cell that the formula references. This shows you which cells feed into the current cell.
Keep clicking on the cells in the chain. Eventually, you will click on a cell whose formula references back to the cell you started with. That is where the loop closes. Write down the cell addresses as you go so you can see the full chain: A1 → B1 → C1 → A1, for example.
You can also use the Trace Precedents tool on the Formulas tab. Click on a cell and then click Trace Precedents. Excel draws blue arrows showing which cells the current cell depends on. Click it again to go one level deeper. This is slower than clicking manually but helps you see the structure visually.
Fixing the circular reference
Once you know the loop, you have three options: change one formula to reference a different cell, delete a formula that should not be there, or move a formula to a different cell.
The most common fix is to change one formula in the chain. For example, if the loop is A1 → B1 → C1 → A1, you might change C1 to reference D1 instead of A1. This breaks the chain. Click on the cell, edit the formula in the formula bar, and press Enter.
Sometimes the fix is to delete a formula entirely. If you have a formula that was added by mistake or copied to the wrong cell, select it and press Delete. The cell will become empty.
After you make a change, run Error Checking again. If the circular reference is gone, the list will be empty. If it is still there, you either did not break the loop or there is a second loop elsewhere in the file.
Checking other sheets for hidden circular references
If Error Checking shows a circular reference but you cannot find it on the current sheet, the loop may involve cells on a different sheet. This happens when a formula on Sheet1 references Sheet2, and a formula on Sheet2 references back to Sheet1.
Look at the cell addresses in the Circular References list. If any of them have a sheet name in front — like Sheet2!A1 — click on that cell address in the list. Excel will switch to that sheet and highlight the cell. Follow the same tracing process on the other sheet.
If you have many sheets, use the Find & Replace tool to search for formulas that reference the problem cell. Press Ctrl+H to open Find & Replace, click Options, and check "Search in formulas". Search for the cell address (for example, =A1) to see which other cells reference it.
Preventing circular references in the future
The best way to avoid circular references is to think about the direction of your formulas before you write them. Decide which cells are inputs (the numbers you type in) and which are outputs (the calculated results). Formulas should always flow from inputs to outputs, never backwards.
When you copy a formula to a new location, check that the cell references have shifted the way you intended. If you copy a formula down a column, the row numbers should increase. If you copy it across a row, the column letters should shift. If they do not, you may have used absolute references (like $A$1) when you meant to use relative ones (like A1).
If you are building a chain of lookups or calculations, sketch it out on paper first. Draw arrows showing which cell references which. If any arrow points backwards, you have found a potential loop before you build it.
When iterative calculation is turned on
If you have turned on iterative calculation in Excel settings, the program will calculate circular references repeatedly until the values stop changing. This is found under File > Options > Formulas > Enable iterative calculation. Most users should leave this off.
If iterative calculation is on and you have a circular reference, Excel will not warn you. The cell will show a number, but it may not be the number you think it is. To check whether iterative calculation is on, go to File > Options > Formulas and look for the checkbox. If it is checked and you did not turn it on deliberately, uncheck it and save the file.
Frequently Asked Questions
Can I have a circular reference that does not show a warning?
Yes, if iterative calculation is turned on in your Excel settings. The program will calculate the loop repeatedly instead of warning you. Check File > Options > Formulas to see if Enable Iterative Calculation is checked. If you did not turn it on, uncheck it.
What if Error Checking does not find the circular reference?
The circular reference may be on a different sheet. Look at the Circular References list and check whether any cell addresses include a sheet name. If they do, click on that address to jump to the other sheet. You can also search for formulas that reference the problem cell using Find & Replace.
Is it ever okay to have a circular reference?
Almost never. Circular references usually mean a formula is in the wrong place or references the wrong cell. If you think you need one, the spreadsheet probably needs to be redesigned. Talk to someone experienced with Excel before turning on iterative calculation.
Will deleting a circular reference lose my data?
Deleting a formula deletes only the formula, not the data in other cells. If the cell contains a calculated result, that result will disappear. If you are not sure whether you need the formula, copy the cell value first (copy, then Paste Special > Values) and paste it somewhere safe before deleting.
Can I fix a circular reference by just pressing Enter?
No. Pressing Enter will accept the circular reference and close the warning dialog, but the loop will still exist. You have to change or delete a formula to break the chain. Use Error Checking to find the cell, then edit the formula.