Circular References in Excel: How to Find and Trace Them

To see circular references in Excel for Microsoft 365 on Windows, open the Formulas tab, choose Error Checking in the Formula Auditing group, then select Circular References. Choose a listed cell to jump to it and inspect its formula. This helps you locate a formula loop that may be stopping a calculation or producing an unexpected result. You will need the workbook open in Excel; a desktop copy is best if you need to trace dependencies in detail.

What You’ll Need

The Excel workbook where you suspect a circular reference. If it is stored in OneDrive or SharePoint, open it in the Excel desktop app for the full auditing tools.
Permission to edit the workbook if you plan to correct a formula. If the file is important, save a separate copy before changing formulas or calculation settings.
A basic understanding of which cells are meant to supply inputs and which are meant to calculate results. This makes it easier to distinguish a genuine mistake from an intentional circular calculation.

Step-by-Step

Open the workbook and check its calculation context. Open the workbook in Excel for Microsoft 365 on Windows and select the worksheet where you think the problem starts. A circular reference can involve cells on different worksheets, so the active sheet is only a starting point. If several workbooks are open, click the one you want to inspect before using the error-checking menu.
Open the Circular References list. Select Formulas > Error Checking > Circular References. Error Checking is in the Formula Auditing group on the Formulas tab. If Excel has detected a circular reference, the submenu shows one or more cell references. Select a reference to move the active selection to that cell. Depending on the workbook, the reference may include a worksheet name.
Read the formula in the selected cell. Look at the Formula Bar above the worksheet, or press F2 to inspect the formula in the cell. Check whether the formula points back to its own cell, either directly or through another formula. For example, if the layout is that A1 is intended to be a standalone input cell, entering =A1+1 in A1 makes the cell refer to itself. Excel cannot calculate that as a normal one-way formula.
Check for a loop across multiple cells. Not every circular reference mentions its own cell directly. Suppose A2 and B2 are calculation cells, and each formula is intended to use the other cell. If A2 contains =B2+1 and B2 contains =A2+1, the formulas depend on each other in a loop. The cell layout in this example is A2 and B2 holding formulas; the first formula belongs in A2 and the second belongs in B2. Inspect every referenced cell, including cells on other sheets, to follow the chain back to its starting point.
Use the list to move between detected references. Return to Formulas > Error Checking > Circular References and select another listed address if one appears. Excel may expose another circular reference after you correct the first one, so do not assume that the initial cell is the only problem. After each repair, check the menu again and continue until the list no longer identifies a loop.
Trace the cell’s formula relationships. Select the problem cell, then use Formulas > Trace Precedents to show cells used by its formula. Use Formulas > Trace Dependents to show cells that rely on it. Follow the arrows and inspect the cells along the path. These tools are useful when a formula refers to another formula, which refers back to the original cell. If arrows remain after your inspection, choose Formulas > Remove Arrows to clear them.
Decide whether the loop is a mistake or intentional. Most circular references happen by accident, such as a total formula that includes its own total cell. In some financial or engineering models, however, a formula is designed to be recalculated repeatedly until it approaches a stable value. Confirm the workbook’s intended design before removing a reference or changing calculation options. If you do not know why the loop exists, ask the workbook owner or compare the formula with a known-good version.
Correct the reference, then check again. Edit the formula so it uses the intended input or range and no longer points back to itself. For a total, exclude the cell containing the total from the summed range; for a copied formula, correct the shifted reference if it now points to the result cell. Press Enter to commit the change, then reopen the Circular References menu. Repeat the inspection and repair for any other listed cells, and confirm that the affected results recalculate as expected.
Review calculation settings only when the model calls for iteration. On Windows, open File > Options > Formulas and look for Enable iterative calculation. If it is enabled, Excel may calculate intentional circular formulas rather than report them like an ordinary mistake. Do not turn it on simply to hide an error. If the workbook is meant to have no circular references, consider disabling it and checking the formulas again; first save a copy because changing this setting can alter workbook results. For a deliberate iterative model, confirm its iteration limits and calculation behavior with the model owner.

Common Mistakes

Checking only the cell with the visible error. The cell displaying an unexpected value may depend on the circular formula without being part of the loop itself. Use the Circular References list to identify the cells Excel flags, then trace the preceding formulas.

Including the total cell in its own range. For example, a sum placed at the bottom of a column can accidentally include its own row after a range is extended. Inspect the start and end of the range rather than assuming the formula is correct because it resembles the totals above it.

Assuming a loop must be direct. A formula can point to a second cell, which points to a third, which eventually points back to the first. Search all relevant sheets and follow the references until the cycle becomes clear.

Enabling iterative calculation as a quick fix. Iteration changes how Excel handles circular formulas; it does not correct an accidental reference. It can make an unintended loop appear to calculate while leaving the workbook logic wrong.

Overwriting a formula before recording it. If you replace a formula without noting its original contents, it can be difficult to restore the intended logic. Copy the formula to a note or make a workbook copy before editing an unfamiliar model.

Tips

Use the Name Box, to the left of the Formula Bar, to jump directly to a listed address if selecting it from the menu is awkward. Type the cell reference and press Enter.
Check formulas that were recently copied, filled down, or edited. Relative references can shift as a formula is copied and may begin pointing to a result cell unexpectedly.
Inspect defined names if the formula shown in the cell does not make the loop obvious. A name used in a formula can refer to a range that leads back to the same formula cell.
Keep an uncomplicated calculation flow where possible: input cells feed calculation cells, and calculation cells feed output cells. Separating these roles makes circular dependencies easier to prevent and diagnose.
When working with several worksheets, note the sheet name as well as the cell address. Two sheets can both contain a cell such as B5, and confusing them can lead to editing the wrong formula.
After correcting a loop, check more than whether the warning disappears. Confirm that the corrected total, balance, or other output still matches the workbook’s intended logic.

Troubleshooting

The Circular References menu is missing or empty. Confirm that the intended workbook is active and that you are using the Excel desktop app. The menu may be empty if Excel has not detected a circular reference in that workbook, or if the issue was already corrected. Check other open workbooks and review the formulas that feed the cell with the unexpected result. If iterative calculation is enabled, determine whether the workbook is intentionally using circular formulas before changing that setting.

The menu lists a cell, but selecting it does not explain the cause. Read the formula in the Formula Bar and follow each cell reference one at a time. Use Trace Precedents and Trace Dependents where helpful, and check other worksheets as well. A formula can also use a defined name, so inspect the name’s reference if the formula is not showing a direct route back to the selected cell.

The warning returns after you fix one reference. There may be more than one loop, or your edit may have exposed another circular reference. Reopen the Circular References submenu after each repair and work through any remaining entries. Also check whether a formula was copied into nearby rows or columns with a similar issue.

The workbook calculates a value even though it contains a loop. Iterative calculation may be enabled, or the workbook may have been designed to use iteration. Check File > Options > Formulas and ask whether iteration is expected. Do not disable it in a shared model without checking the consequences, because results that rely on repeated calculation may change.

You are using Excel for the web or a Mac. Excel for the web may not offer the same complete formula-auditing experience as the Windows desktop app; open the workbook in desktop Excel to use the menu and trace tools described above. On Mac, use the Formulas tab and look for Error Checking and Circular References; ribbon placement can differ by version. The Windows shortcut F2 may require Fn+F2 on some Mac keyboards, and the Mac equivalent for editing the selected cell is commonly Control+U. Check your keyboard settings if those keys behave differently.

Frequently Asked Questions

What does a circular reference mean in Excel?
A formula has a circular reference when its calculation depends on itself, either directly or through other cells. Excel cannot resolve it as a normal one-direction calculation unless the workbook is configured for iterative calculation.

Where do I find the Circular References command?
In Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References. The submenu lists detected cell references that you can select to jump to the corresponding cells.

Can I find a circular reference across multiple sheets?
Yes. A loop can pass between worksheets. Follow the formulas and check each referenced sheet; do not limit the search to the sheet where the warning or unexpected result first appeared.

Why does Excel show no circular reference when I expect one?
You may have the wrong workbook active, the formula may already have been corrected, or iterative calculation may be enabled. Check the workbook’s calculation settings and trace the formulas feeding the affected result. For full auditing, use the desktop app.

Should I enable iterative calculation to remove the warning?
Only if the workbook is intentionally designed to calculate a circular model. Iteration is not a general-purpose repair. For an accidental loop, correct the cell references instead so the calculations follow the intended logic.

How do I remove the arrows after tracing formulas?
Choose Formulas > Remove Arrows. This clears the auditing arrows from the worksheet; it does not change or delete the formulas.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.