A circular reference in Excel happens when a formula refers back to its own cell, either directly or through a chain of other cells. Excel cannot calculate this. It either shows a zero, gives you a warning, or loops forever.
If you have seen the message “There are one or more circular references where a formula refers to its own cell either directly or indirectly,” you are in the right place.
What Is a Circular Reference in Excel
Before finding one, you need to know what it looks like.
Direct circular reference: Cell A1 contains =A1+10. The formula is inside the very cell it is trying to calculate.
Indirect circular reference: Cell A1 contains =B1+5, and Cell B1 contains =A1+5. They feed each other in a loop.
Excel cannot resolve either case without a starting point. That is why it warns you or defaults to zero.
How to Find Circular References in Excel: The Fast Way
Go to the Formulas tab on the ribbon. Click the small arrow next to Error Checking. Hover over Circular References. Excel will show you a list of every cell that has a circular reference in the current sheet.
Click any cell in that list. Excel jumps directly to it.
That is the fastest way. Done in under 10 seconds.
Step-by-Step: Finding Circular References in Detail
Step 1: Open the Error Checking Menu
- Click the Formulas tab in the ribbon
- Find the Formula Auditing group
- Click the dropdown arrow on the Error Checking button
- Hover over Circular References
If circular references exist, the submenu shows cell addresses like $B$4, $C$12. If the submenu is greyed out and says “Circular References” with nothing listed, your workbook is clean.
Step 2: Navigate to Each Circular Reference
Click each cell address shown in the submenu. Excel takes you straight to that cell. Look at the formula bar to see what the formula is doing. Ask yourself: does this formula reference itself or another cell that eventually comes back to it?
Step 3: Check the Status Bar
Look at the very bottom left of your Excel window (the status bar). If any circular reference exists in the workbook, Excel displays the word “Circular References” followed by the cell address. This is always visible even when you are not in the Formulas tab.
If you do not see it, either there are no circular references, or the status bar is customized to hide it. Right-click the status bar to turn that indicator on.
Step 4: Use Trace Precedents and Dependents
Once you find the circular reference cell, use the Trace Precedents and Trace Dependents buttons (also in the Formula Auditing group) to see blue arrows connecting the cells involved in the loop.
This visual map makes indirect circular references much easier to understand.
Finding Circular References Across Multiple Sheets
This is where many people get stuck. The Error Checking menu only shows circular references on the active sheet. If the circular reference lives on another sheet, you need to switch to that sheet first.
How to check all sheets:
- Click through each worksheet tab one by one
- After clicking each tab, go to Formulas > Error Checking > Circular References
- If the submenu shows a cell address, you found one on that sheet
- Repeat for every sheet in the workbook
There is no single button that scans every sheet at once in standard Excel. You have to check them manually sheet by sheet. For large workbooks with many tabs, this takes time but it is the only reliable method.
Tip: The status bar method helps here too. Click each sheet tab and glance at the bottom left. If “Circular References” appears in the status bar, that sheet has one.
What Causes Circular References
Understanding the cause helps you avoid them.
| Cause | Example |
|---|---|
| Formula referencing its own cell | =A1*2 placed in cell A1 |
| Two cells referencing each other | A1=B1+1 and B1=A1+1 |
| SUM range includes the SUM cell | =SUM(A1:A10) placed in cell A5 |
| Copying formulas without fixing references | Dragging a formula incorrectly |
| Named ranges that loop back | A named range that includes the cell using it |
The SUM range mistake is extremely common. People drag a SUM formula down a column and accidentally include the result cell inside the range.
How to Fix a Circular Reference
Finding it is half the job. Fixing it is the other half.
Option 1: Rewrite the Formula
The most reliable fix. Look at what you actually want the formula to calculate. Then rewrite it so it does not reference itself. Usually this means:
- Changing the SUM range to exclude the formula cell
- Moving the result to a separate column or row
- Breaking the feedback loop between two cells by using a static value in one of them
Option 2: Use Iterative Calculation (Only When You Actually Need It)
Excel has an Iterative Calculation setting. When turned on, Excel recalculates a set number of times (default: 100 iterations) to try to resolve circular references.
To enable it: File > Options > Formulas > Enable iterative calculation.
This setting is designed for specific engineering or financial models that use circular logic intentionally (like a loan interest calculation that depends on itself). Most everyday users should not turn this on. It hides the problem without solving it and can produce wrong results silently.
If you are not building a deliberate iterative model, fix the formula instead.
Option 3: Replace with a Hardcoded Value
Sometimes a cell that is causing a loop just needs a static number. Delete the formula and type the value directly. This is perfectly acceptable when the cell does not need to update dynamically.
Practical Examples
Example 1: SUM That Includes Itself
You have data in A1:A9 and you place your SUM formula in A10:
=SUM(A1:A10) placed in cell A10.
The range A1:A10 includes A10 itself. Excel warns you about a circular reference.
Fix: Change the formula to =SUM(A1:A9).
Example 2: Running Total Gone Wrong
You want a running total. In column B, you write:
=B1+A2 placed in cell B2, then =B2+A3 in B3, and so on. This is fine.
But if someone accidentally writes =B2+A2 in B2, B2 references itself. Circular reference.
Fix: Make sure the previous row’s B cell feeds into the next, not the current row’s own cell.
Example 3: Profit Formula That References Itself
Cell D5 contains: =D5-C5 (trying to calculate profit but using D5 as part of the formula).
This is a direct circular reference. The formula should be =B5-C5 using revenue from column B instead.
Circular References in Excel Tables and Named Ranges
Structured Excel Tables (created with Ctrl+T) and named ranges can create harder-to-spot circular references. The formula may look clean but the named range it references includes the formula cell.
To check named ranges: go to Formulas > Name Manager. Review each named range and see what cells it refers to. Then look at any formulas using that named range and confirm the formula cell is not inside the range.
Microsoft’s official Excel support documentation covers named range behavior in more depth if you need it: support.microsoft.com.
How to Prevent Circular References
Prevention is faster than debugging.
- Always double-check your SUM ranges before pressing Enter
- Use the formula bar preview to see what cells a formula references before confirming
- Turn on the Show Formulas view (Ctrl + `) to scan a sheet visually for self-referencing patterns
- Avoid copying formulas across large ranges without checking where the references land
- Use Trace Precedents before finalizing complex formulas that link across sheets
Circular References and Excel Performance
Even if Excel appears to calculate correctly with iterative calculation turned on, circular references slow down your workbook. Each recalculation triggers multiple passes through the loop. On large workbooks, this becomes noticeable. Clean formulas with no circular references always calculate faster.
Finding Circular References
| Method | Where to Find It | Best For |
|---|---|---|
| Error Checking menu | Formulas tab > Error Checking > Circular References | Quick lookup on active sheet |
| Status bar indicator | Bottom left of Excel window | Passive monitoring |
| Trace Precedents/Dependents | Formulas tab > Formula Auditing | Visualizing the loop |
| Sheet-by-sheet manual check | Click each tab, then check Error Checking | Multi-sheet workbooks |
| Show Formulas view | Ctrl + ` | Visual scan of all formulas |
Conclusion
Circular references are one of the most common Excel errors, and they are easy to fix once you know where to look. Start with Formulas > Error Checking > Circular References on each sheet. Use the status bar as a quick indicator. Use Trace Precedents to map out indirect loops. Then fix the formula by rewriting it so no cell references itself directly or through a chain.
Frequently Asked Questions
Why does Excel show a circular reference warning when I open a file?
Excel detects that at least one formula in the workbook references its own cell or creates a loop with other cells. The warning appears every time the file is opened if the circular reference has not been resolved. Follow the steps above to find and fix it.
Can a circular reference give wrong results without showing an error?
Yes. If iterative calculation is turned on, Excel may silently calculate a value without warning you. The result may look correct but could be inaccurate depending on the number of iterations and the convergence of the formula. Always verify results when iterative calculation is enabled.
How do I find circular references in Excel on Mac?
The process is the same on Mac. Go to the Formulas tab, click the arrow next to Error Checking, and hover over Circular References. The status bar at the bottom also shows “Circular References” when one exists on the active sheet.
Why is the Circular References option greyed out in the Error Checking menu?
This means there are no circular references on the currently active sheet. Switch to other sheets and check each one. If you are sure there should be one, check whether iterative calculation is enabled under File > Options > Formulas, as Excel may have already resolved it through iteration.
Does deleting the formula and retyping it fix a circular reference?
Sometimes yes, if you retype it correctly without including the formula cell in its own range. Simply deleting and retyping the same formula in the same cell will reproduce the same circular reference. You need to change the formula logic, not just re-enter it.
