To trace a circular reference in Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References, then select a listed cell to jump to it. Follow its formula links with Trace Precedents and Trace Dependents until you find the formula that leads back to the starting cell; then correct the unintended loop or enable iteration only if the circular calculation is deliberate.
What a circular reference means
A formula has a circular reference when its result depends, directly or indirectly, on its own value. The simplest case is a formula in A1 that refers to A1. A less obvious case is a chain: A1 refers to B1, B1 refers to C1, and C1 refers back to A1. Each formula may look reasonable by itself, but Excel cannot calculate the chain in the usual one-pass way because the final value depends on itself.
Some circular references are accidental: a total formula includes its own total cell, or a copied formula points back into the range it is meant to summarize. Others are intentional. Certain financial or engineering models use repeated calculations in which a value is refined using its previous result. Excel can run those calculations using iterative calculation, but iteration should not be switched on merely to hide a warning. First establish whether the loop is intended and whether its calculation logic is sound.
Find the circular-reference cell
Start with Excel’s built-in list. On Windows, open the Formulas tab, open the Error Checking dropdown in the Formula Auditing group, and choose Circular References. If Excel has detected a circular reference, the menu lists a cell address. Select an address to make that cell active and inspect its formula in the formula bar. If several references are listed, investigate each one; more than one loop can exist in a workbook.
The status bar at the bottom of Excel can also report that a circular reference exists, sometimes with a cell address. Treat it as a clue rather than a complete diagnosis: the address may not identify every cell in the loop, and references involving another worksheet can take more investigation. If the Circular References menu is unavailable or empty, check whether the workbook is currently recalculating, whether you are looking at the right open workbook, and whether the warning has already been resolved.
Excel for Mac has similar circular-reference and formula-auditing controls on the Formulas tab, although the ribbon layout can differ by release. In Excel for the web, available auditing features may differ from the desktop app. If you cannot find the list or tracing commands in the web interface, open the workbook in the desktop version of Excel.
Trace the formula loop with arrows
Once you have selected a cell in the loop, use the Formula Auditing commands on the Formulas tab. Choose Trace Precedents to display arrows from cells used by the selected formula. Choose Trace Dependents to show cells whose formulas use the selected cell. These arrows show relationships between formulas, helping you follow the chain rather than guessing from cell addresses.
- Select the circular-reference cell from the Circular References list.
- Choose Trace Precedents and inspect the cells feeding into its formula.
- Select a precedent cell in the chain and trace its precedents in turn. Continue until a formula refers back to the original cell or another cell already visited.
- Use Trace Dependents when it is easier to follow the chain in the other direction, from the starting cell toward formulas that rely on it.
- After making a correction, remove the on-screen arrows with Formulas > Remove Arrows so they do not clutter the worksheet.
Tracing is a visual aid, not an automatic repair tool. A formula can depend on many cells, and a long chain may require selecting and tracing several cells. If the formula refers to another worksheet or workbook, follow the sheet or workbook reference as well. A trace may not make a distant or external dependency immediately obvious, so read the formula bar and verify the referenced sheet and cell addresses.
Read formulas and verify the cycle
Click each relevant cell and inspect the formula bar. Write down the sequence of references, including worksheet names where they appear. The goal is to identify a closed path: for example, A1 depends on B1, B1 depends on C1, and C1 depends on A1. A cell can also contain a direct self-reference. Distinguishing the actual loop from cells that merely feed into it prevents you from changing unrelated formulas.
For a more controlled inspection, select a formula cell and choose Formulas > Evaluate Formula. The dialog steps through parts of that formula so you can see how Excel evaluates its references. This is useful when the formula contains nested functions or long expressions, but it does not automatically map every circular chain across a workbook. Use it alongside the Circular References list and trace arrows.
When a worksheet is unfamiliar, it may help to display formulas instead of results using Formulas > Show Formulas. This changes the worksheet view, not the underlying calculations. Turn it off from the same command when you are done. You can also search for formulas using Find, but searching is not a dependable way to detect every circular reference: a formula may point to a cell through several intermediate formulas, and a text search cannot establish that full dependency path.
Common causes and a simple example
A frequent mistake is including a result cell in the range it is supposed to total. Suppose B2:B5 contain amounts and B6 is meant to display their total. With that layout, this formula in B6 includes B6 itself and creates a circular reference: =SUM(B2:B6). The intended range excludes the result cell. With the same layout, put this corrected formula in B6: =SUM(B2:B5). The total then adds only the four input cells.
Another common cause is copying a formula down or across without checking how its references shift. A formula that was correct in one row can, after filling, point to a result cell or a range that includes itself. Mixed and absolute references can also make a copied formula behave differently from what the author expects. Click the suspicious cells and compare their actual formulas rather than relying only on how the first formula was written.
Here is a small two-cell loop. Assume A1 and B1 are intended to contain related calculations. With those cell positions, enter =B1+1 in A1, and with the same layout enter =A1-1 in B1. A1 depends on B1 and B1 depends on A1, so neither has an independent starting value. This example illustrates why a pair of individually simple formulas can still form a circular reference; it is not a useful calculation model as written.
Other causes include a total that includes its own row, a formula that points to a cell containing a downstream result, or a chain crossing multiple worksheets. Imported or edited workbooks can also contain old formulas that are no longer appropriate after rows or columns are rearranged. In each case, trace the references to find the link that closes the loop, then decide what the formula was meant to calculate.
Fix an unintended circular reference
Most accidental loops are fixed by changing one formula or its input range so that the calculation has a clear direction. For a total, exclude the total cell from the summed range. For a copied formula, correct the reference or use an appropriate absolute reference if the formula should keep pointing to a fixed input. For a multi-sheet loop, decide which sheet should provide the independent input and remove the backward dependency.
Before changing a complex workbook, save a separate copy. Then make one targeted edit and check whether the circular-reference list clears and whether the resulting values make sense. A disappearing warning is not proof that the workbook is correct: compare results with a known example, check key totals, and inspect formulas near the edited cell. If other formulas depend on the changed cell, use Trace Dependents to understand the possible effects.
Do not replace a formula with a typed number just to silence the warning unless that is genuinely the intended design. Doing so can break future updates and conceal the original dependency problem. If the calculation needs a previous-period value, a beginning balance, or another independent assumption, model that input explicitly rather than making a formula refer back to its own output.
When iterative calculation is appropriate
Some models intentionally use a circular calculation to repeat an estimate until it stabilizes. In Excel for Microsoft 365 on Windows, go to File > Options > Formulas and select Enable iterative calculation. The same area provides controls for the maximum number of iterations and the maximum change used to stop the calculation. A smaller allowed change can require more calculation; a limit on iterations prevents Excel from recalculating indefinitely.
Enable iteration only when the workbook’s author or model documentation explains why the loop is necessary and what a satisfactory stopping point means. Check the relevant input values and results after enabling it. Iterative calculation changes how Excel handles circular formulas, but it does not determine whether the model’s assumptions are valid. A circular reference caused by a mistaken total should be repaired, not treated as an intentional iterative model.
On Mac, look under Excel’s application settings or preferences for the calculation options; wording and location vary by Excel release. Excel for the web does not expose all desktop calculation controls in the same way, so use the desktop app when you need to configure or carefully audit iterative calculation. If a workbook with iteration is shared, tell other users that its calculations intentionally rely on iteration and confirm that their Excel environment handles the workbook as expected.
Calculation settings and other limitations
Excel’s calculation mode can affect when formulas update, but changing calculation from Manual to Automatic does not remove a circular reference. On Windows, check Formulas > Calculation Options if values appear stale. Manual calculation can make a workbook look as though a repair did not take effect until recalculation occurs; it does not explain away a genuine dependency loop.
Trace arrows also have practical limits. They show formula relationships, not the meaning of the business rule behind them, and a complex workbook may contain several loops or a long chain of dependencies. External workbook links, hidden worksheets, and formulas that depend on changing inputs can make a loop harder to spot. Work through the cells systematically, and keep a note of the path so you can distinguish the circular path from ordinary precedents.
Frequently Asked Questions
Where is the Circular References command in Excel?
On Windows, open Formulas > Error Checking and choose Circular References. Select a listed cell to go to it. The command is available when Excel has detected a circular reference in the open workbook.
How do I trace a circular reference across worksheets?
Start from the listed cell, inspect its formula for a sheet reference, and use Trace Precedents or Trace Dependents to follow the linked cells. Move to the referenced worksheet and continue until the path returns to a cell already in the chain. Check formulas on each sheet rather than assuming the first listed cell is the whole loop.
Why does Excel say there is a circular reference when I cannot see one?
The loop may be indirect: the selected formula points to a second cell, which points to a third, and that third cell points back. It may also cross sheets. Use the Circular References list and trace arrows, then inspect the formula bar at every step instead of looking only at the first cell.
Should I turn on iterative calculation to clear the warning?
Only if the circular calculation is intentional and the workbook’s logic calls for repeated calculations. Iteration changes how Excel calculates the loop; it does not fix an accidental self-reference or prove that the result is correct. Repair unintended loops first.
Can I trace circular references in Excel for the web?
Some auditing features and controls differ between Excel for the web and the desktop app. If the Circular References list or the tracing tools are unavailable in your browser, open the workbook in Excel for Microsoft 365 on Windows or Mac and use the Formulas tab there.
Related
- Excel Solver Parameters: How to Set Them Correctly
- Excel Slicers for Multiple Tables: What Works
- Excel Slicers: Advanced Features and Smart Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- F4 Dollar Sign in Excel Not Working? Causes and FixesIf F4 will not add dollar signs to a cell reference in Excel for Microsoft 365 on Windows,…
- Excel Stuck on Not Responding? Causes, Fixes, and RecoveryIf Excel is stuck on Not Responding, first give it a few minutes if it may be calculating,…
- XLOOKUP Excel Not Working? Fix Common Errors and CausesIf XLOOKUP is not working, first check that your Excel version supports it, then confirm the lookup and…
- Data Validation Missing in Excel? How to Restore ItIf Data Validation is missing in Excel, first check that you are using the desktop app and look…
- Excel Filter Not Working? Fix the Most Common CausesIf an Excel filter is not working, first check that it covers the complete data range and that…
- Why Is My VLOOKUP Not Working? Excel FixesVLOOKUP usually fails because the lookup value does not exactly match the first column of its table, the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.