If Excel is not showing a circular reference, open Formulas > Error Checking > Circular References and check the status bar for a circular-reference message and cell address. If the list is empty, the loop may be on another worksheet, hidden inside a name or formula, already fixed, or not actually circular; trace the formulas and check calculation settings before assuming Excel missed it.
What a circular reference means in Excel
A circular reference occurs when a formula depends on its own result, either directly or through other cells. Excel cannot calculate a straightforward final answer from that dependency because the result depends on itself. The circular link might be obvious in one formula, or it might pass through several cells and worksheets before returning to the starting point.
For example, with B5 intended to be a total and B2:B5 containing the values included in that total, the formula in B5 is circular: =SUM(B2:B5). The range includes the formula cell, so calculating B5 requires knowing B5 first. If B2:B4 contain 10, 20, and 30, the intended total is 60, but this formula does not produce that ordinary sum because B5 includes itself in the range.
A circular reference is not the same as a #REF! error. A #REF! error generally means that a formula refers to a cell reference that is no longer valid. A circular reference is a dependency loop. Excel may show a warning when a loop is first entered, but an existing loop can remain in a workbook without that warning appearing every time the file opens.
Where to find Excel’s circular-reference warning
In Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References. If Excel has identified a circular reference, this menu can show a cell address. Select an address to go to that cell, then inspect its formula and the cells it refers to. If there is more than one loop, Excel may require you to resolve one before the remaining references are easier to identify.
Also look at the status bar along the bottom of the Excel window. When a circular reference is active, Excel can display a circular-reference message and a cell address there. The ribbon list and status bar are useful starting points, but neither is a complete explanation of the dependency chain: the address identifies a cell involved in the problem, not necessarily every cell that completes the loop.
If you have just typed a formula, Excel may display a warning dialog saying that the formula contains a circular reference. Dismissing the dialog does not repair the formula. A workbook that already contains a loop may not show the same prompt again, so use the ribbon command and status bar rather than relying only on a popup.
Why the circular-reference list may look empty
An empty list does not always mean the workbook has never had a circular reference. The formula may already have been corrected, the relevant workbook may not be active, or the problem may be in another worksheet or a less obvious part of a formula. Check these possibilities:
- Check the active workbook and sheet. Make sure you are inspecting the file that contains the calculation. A workbook can contain multiple sheets, and the cell shown in the error list may be on a different sheet.
- Recalculate the workbook. If calculation is set to Manual, formula results and error indicators may not update when you expect. On Windows, check Formulas > Calculation Options. Automatic calculation is the usual setting for ordinary models; do not change it casually in a large workbook where manual calculation is deliberate.
- Inspect formulas that were recently edited. A circular-reference warning can be dismissed, and an earlier formula may have been changed since the warning appeared. Use the current formula bar contents, not an old warning, to diagnose the workbook.
- Check names and cross-sheet references. A defined name can refer to a range or formula that leads back to the original cell. A formula on one sheet can also depend on another sheet that eventually refers back to the first.
- Confirm that the apparent loop is a real dependency. Similar cell addresses, a formula that points to a nearby cell, or a number that does not update are not by themselves proof of a circular reference.
Calculation settings can affect when Excel updates formulas, but they do not turn an ordinary dependency chain into a circular reference. Likewise, a stale displayed value can be a calculation or data-refresh issue rather than a circular loop.
Trace direct and indirect formula loops
Start with the cell Excel lists, select it, and read the formula in the formula bar. Follow each referenced cell until you find a path back to the original cell. For a simple loop, Excel’s formula-auditing commands can help: select a formula cell and use Formulas > Trace Precedents to show cells feeding into it, or Formulas > Trace Dependents to show cells that rely on it. Arrows can cross worksheets, and you may need to select other cells in the chain to continue tracing.
The Evaluate Formula command, available from Formulas > Evaluate Formula, steps through how Excel evaluates a formula. It can make a complicated expression easier to understand, but it is not a substitute for following references across the workbook. For larger workbooks, search formulas for the relevant cell address, inspect defined names in Name Manager, and check formulas on sheets that feed the affected calculation.
Indirect loops are a common reason the problem is hard to spot. With A1 and B1 containing formulas that refer to each other, the first formula is =B1+1 and the second is =A1+1. The cell layout is A1 and B1 on the same worksheet, with each cell containing the formula shown; neither has a defined result because each requires the other’s result first. This is a circular dependency even though neither formula directly refers to its own cell.
Other less-visible sources include formulas using defined names, formulas in table totals, and links between worksheets. A formula can also refer to a range that accidentally includes its own cell. When a formula appears reasonable, inspect the actual range boundaries and expand any names used in it before concluding that Excel has made a false detection.
Fix the formula rather than hiding the warning
For an accidental circular reference, change the formula or the workbook layout so that the calculation no longer depends on its own answer. In the total example, if B2:B4 contain 10, 20, and 30 and B5 is the total, the cell layout should keep the inputs in B2:B4 and put the total in B5. Enter =SUM(B2:B4) in B5. It returns 60, because 10 + 20 + 30 = 60, and the total cell is no longer included in its own input range.
For a loop spread across cells, decide which cell should be the source of each value. Sometimes a formula has been entered in the wrong row or column; moving it can break the loop. In other cases, one formula needs a prior-period value or a separate input cell instead of a result that depends on the formula itself. Make the change in a copy of a complex workbook, then check dependent totals and reports to confirm that the revised logic still matches the intended calculation.
After making a correction, return to Formulas > Error Checking > Circular References and check whether another listed cell remains. Recalculate if needed, and verify the values that depend on the changed formula. Avoid deleting a formula just to remove the warning unless you know what the formula calculates; doing so can replace a visible problem with missing or incorrect results elsewhere.
When iterative calculation is appropriate
Some financial, engineering, and planning models intentionally use repeated calculations to approach a stable result. Excel can permit this behavior through iterative calculation. This is different from fixing an accidental loop: enabling iteration changes how Excel calculates circular formulas, and it may make an unintended circular reference appear to work.
On Windows, find the setting under File > Options > Formulas, then select Enable iterative calculation. You can set the maximum number of iterations and the maximum change. The iteration limit controls how many times Excel recalculates the circular formulas; the maximum-change setting is a stopping threshold for changes between calculations. Choose values based on the model’s design, not simply to suppress a warning.
Before enabling iteration, document why the model needs it and test whether its results converge as expected. A formula loop that oscillates, changes very slowly, or depends heavily on starting values can produce results that are misleading for the intended use. If you do not understand why a workbook needs iterative calculation, first look for an accidental reference or ask the workbook’s author. Excel for Mac has calculation settings under Excel > Settings (called Preferences in some releases), then Calculation; wording and layout can vary by release.
Excel for Mac and Excel for the web
The menu names and layout can differ slightly on Mac, but the core task is the same: inspect formula errors and follow the referenced cells. In Excel for Mac, look for Circular References in the Formulas tab’s error-checking controls if available in your release, and use formula-auditing tools to trace dependencies. If a menu item is not where expected, consult the ribbon’s search feature or Excel Help for that installed version.
Excel for the web can be useful for viewing and editing workbook formulas, but the desktop applications provide more complete formula-auditing and calculation controls. If the web interface does not expose the circular-reference list or the iteration settings you need, open the workbook in Excel for Microsoft 365 on Windows or Mac. Be careful when switching between versions if a workbook depends on iterative calculation or other advanced workbook features.
Common misconceptions and useful checks
A circular-reference message does not necessarily mean the workbook is corrupted. It usually means Excel found a formula dependency that loops back on itself. The right fix depends on whether that loop is accidental or deliberately part of a model. Conversely, not every unexpected zero or unchanged result is a circular reference; check calculation mode, formula inputs, and data refreshes too.
It is also easy to confuse circular references with formulas that use the same value repeatedly. Reusing an input cell in multiple formulas is normal as long as there is no dependency path returning to the formula cell. What matters is the direction of the dependencies, not whether a cell address appears in many places.
When diagnosing a workbook, take note of the exact cell address Excel reports, the formula in that cell, and the next cells in its dependency chain. If the workbook is shared, communicate whether you removed an accidental loop or enabled iteration on purpose. That distinction matters because another person may otherwise treat a deliberate calculation setting as an error or unknowingly introduce a new loop while editing.
Frequently Asked Questions
Why does Excel show a circular reference warning but no cell address?
Excel may display a warning when a formula is entered, while the current ribbon list or status-bar message is not obvious. Check Formulas > Error Checking > Circular References in the active workbook, then follow the formula references. The warning alone may not identify the full dependency chain.
Can a circular reference be on another sheet?
Yes. A formula on one sheet can refer to a cell on another sheet, whose formula eventually points back to the first. Follow precedent and dependent arrows across sheets and inspect the formulas at each step.
Why does my circular-reference formula return zero?
A zero does not prove that a circular formula calculated correctly. Excel’s displayed result can depend on the workbook’s calculation state and settings. Check the circular-reference list and formula chain, and remove an accidental loop rather than relying on the displayed number.
Should I enable iterative calculation to remove the warning?
Only if the workbook intentionally uses circular calculations and you understand the model’s iteration requirements. Iteration is not a general-purpose repair. For an accidental loop, edit the formula or layout to remove the dependency cycle.
How do I find every circular reference in a large workbook?
Use the Circular References command, resolve a reported loop, and check the list again. Then inspect formulas, defined names, and cross-sheet dependencies if the result still seems incomplete. A workbook can contain more than one circular path, so verify the remaining formulas after each fix.
Related
- Personal Budget Spreadsheet Template: Build One in Excel
- Debt Snowball Spreadsheet: Ramsey Method in Excel
- Organize a Budget Spreadsheet in Excel: Setup and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets QUERY: Syntax, Examples, and Tips ExplainedIn Google Sheets, QUERY is a function that retrieves, filters, sorts, groups, and summarizes rows from a range…
- Power Query Replace Text: Steps, Options, and ExamplesTo replace text in Power Query, open the Power Query Editor, select the column, and choose Transform >…
- Conditional Formatting Based on Another CellYes. In Excel for Microsoft 365, you can make a cell or range change format based on another…
- Excel Workbook Statistics: What It Counts and How to Use ItExcel’s Workbook Statistics feature gives you a quick count of items in the active worksheet or the whole…
- Excel: Calculate Percentage Difference Between NumbersIf A2 contains the first number and B2 the second, calculate the percentage difference with =ABS(A2-B2)/AVERAGE(ABS(A2),ABS(B2)), then format…
- Excel Shopping List Template: Create, Organize, and PrintAn Excel shopping list template is a worksheet for tracking what to buy, how much you need, where…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.