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. If A1 contains =B1, B1 contains =C1, and C1 contains =A1, that is an indirect circular reference — the cells point to each other in a loop.
Excel cannot solve a circular reference because there is no starting point for the calculation. 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 box telling you one exists, but it does not tell you where.
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 in, or when you build a chain of formulas that loops back without meaning to. Occasionally someone creates one on purpose to model a repeating calculation, but that requires turning on iterative calculation in Excel settings — and even then, it is fragile and hard to maintain.
Key Takeaways
- Excel shows a warning when you open or create a file with a circular reference, but does not say which cell contains it.
- Use the Circular Reference tool in the Formulas tab (Windows) or Formulas menu (Mac) to find the cell or cells involved.
- The most common cause is copying a formula to a cell that the original formula already references.
- Delete or edit the formula in the flagged cell to break the loop, then save the file.
- If you see a circular reference warning but cannot find the problem, check for hidden rows, hidden columns, and named ranges that might contain formulas.
Finding a circular reference in Windows Excel
When Excel shows the circular reference warning, click OK to close the dialog. Then go to the Formulas tab at the top of the ribbon. In the Formula Auditing group, you will see a button called Circular Reference with a dropdown arrow next to it. Click the dropdown.
Excel will show you a list of all cells that are part of a circular reference loop. Click on the first cell in the list. Excel will jump to that cell and highlight it. Look at the formula bar at the top to see what the cell contains. Check whether that formula refers to itself or to another cell in the loop. If the file has only one circular reference, the list will show one or two cells. If the file has multiple separate loops, the list will show all of them.
Once you have identified the problem cell, you can edit or delete the formula. Click in the formula bar, make your change, and press Enter. The circular reference warning should disappear. If it does not, click the Circular Reference dropdown again to see if there are more loops.
Finding a circular reference on Mac Excel
On Mac, the process is similar but the menu location is different. When you see the circular reference warning, click OK. Then click the Formulas menu at the top of the screen (not the ribbon tab — the actual menu bar). You will see an option called Circular Reference with an arrow indicating a submenu.
Hover over or click Circular Reference to see the list of cells involved in the loop. Click on the first cell. Excel will navigate to it and show the formula in the formula bar. Edit or delete the formula as needed, then press Enter. Check the Formulas menu again to confirm the circular reference is gone.
Common reasons circular references happen
The most frequent cause is copying a formula down or across a range and accidentally including the cell the formula started in. For example, if A1 contains =SUM(A2:A10) and you copy that formula to A2, then A2 will contain =SUM(A3:A11) — which is correct. But if you copy A1 to A10, then A10 will contain =SUM(A11:A20), which is fine. However, if you copy A1 to A1 again (or select A1:A10 and paste), A1 will try to reference itself.
Another common mistake is editing a formula and accidentally typing the cell's own address into it. If you are in cell B5 and you type =B5+C5, you have created a circular reference. This often happens when you are working quickly or when the cell reference is not obvious from the context.
A third source is building a chain of lookups or references that eventually loops back. For instance, if A1 references B1, B1 references C1, and C1 references A1, Excel cannot calculate any of them. This is less common than the first two causes but harder to spot because the loop is not obvious from looking at a single cell.
How to fix a circular reference
Once you have found the cell, decide what the formula should actually be. Look at the cells around it and the row or column headers to understand what calculation was intended. Delete the incorrect formula and type the correct one, or delete the formula entirely if the cell should contain a value instead.
If you are not sure what the formula should be, undo your recent changes by pressing Ctrl+Z (Windows) or Command+Z (Mac) several times to go back to a version before the circular reference appeared. Then redo your work more carefully, checking the formula bar as you go.
After you fix the problem, save the file. The next time you open it, Excel will not show a circular reference warning. If you want to be thorough, use the Circular Reference tool one more time to confirm the list is empty.
When circular references are hidden in named ranges or hidden cells
Sometimes a circular reference exists but is hard to find because it is in a named range, a hidden row, or a hidden column. If the Circular Reference tool shows a cell address but you cannot find it on the visible sheet, check whether that row or column is hidden.
To unhide rows, select the rows on either side of the hidden ones, right-click, and choose Unhide. To unhide columns, do the same with columns. Once the hidden cells are visible, you can see and fix the formula.
If the circular reference is in a named range, go to the Formulas tab (Windows) or Formulas menu (Mac) and look for Manage Names or Define Names. Find the name in the list and click it to see what cells it refers to. If the range includes a cell with a circular formula, edit or delete that formula to break the loop.
Preventing circular references in the future
The best way to avoid circular references is to be deliberate about which cells contain formulas and which contain values. Keep data in one area and calculations in another. For example, put raw numbers in rows 2 through 100, and put summary formulas in row 101. That way, a summary formula cannot accidentally reference itself.
When you copy a formula, always check the formula bar afterward to make sure the cell references changed the way you expected. If you are copying down a column, the row numbers should increase. If you are copying across a row, the column letters should shift. If they do not, you may have used absolute references (with dollar signs) when you meant to use relative ones.
Use the Trace Precedents and Trace Dependents tools in the Formulas tab to see which cells a formula depends on and which cells depend on it. This visual map can help you spot loops before they cause problems.
Frequently Asked Questions
Can I ignore a circular reference warning and keep working?
Excel will let you save a file with a circular reference, but the affected cells will show an error or zero instead of a calculated value. The file will work, but those cells will not calculate correctly. It is better to fix the problem right away.
What if the Circular Reference tool shows a cell that does not exist?
This usually means the cell was deleted but the formula referring to it was not. Check the formula bar for any cell addresses that are no longer on the sheet, or look in hidden rows and columns. You may also need to check named ranges to see if they reference deleted cells.
Can I have more than one circular reference in the same file?
Yes. A file can have multiple separate loops. The Circular Reference tool will list all of them. Fix one loop at a time, then check the tool again to see if more remain.
Does turning on iterative calculation fix a circular reference?
No. Iterative calculation lets Excel recalculate a circular formula a set number of times to try to reach a stable answer, but it does not fix the underlying problem. It is a workaround for intentional circular models, not a solution for accidental ones. Most users should leave it off.
What if I created the circular reference on purpose?
If you are modeling a repeating calculation that genuinely needs to reference itself, you can turn on iterative calculation in Excel options. On Windows, go to File > Options > Formulas and check Enable Iterative Calculation. On Mac, go to Excel > Preferences > Formulas and Calculation. Set the maximum iterations and maximum change values. However, this approach is fragile and hard to audit, so consider whether a different formula structure would work instead.