Detect Circular References in Excel: Step-by-Step

How-to basics · Updated November 18, 2026

To detect a circular reference in Excel for Microsoft 365 on Windows, open the Formulas tab, choose Error Checking ▼, then select Circular References. Choose a listed cell to jump to it, then trace its formula’s dependencies to find the loop. A circular reference occurs when a formula depends on its own result, either directly or through other cells.

What You’ll Need

  • The workbook in which Excel reports a circular reference, or a workbook whose results appear stuck or unexpected.
  • Excel for Microsoft 365 on Windows for the menu paths below. The same checks are available in many recent desktop versions, although labels can vary slightly.
  • Permission to edit the workbook if you plan to correct a formula. If it is shared or business-critical, save a separate copy before making changes.

You do not need an add-in or special formula to find a circular reference. Excel’s built-in error-checking menu is the quickest place to start.

Step-by-Step

  1. Check for an alert when you enter a formula. If Excel detects a new circular reference while you are typing or editing a formula, it may show a warning explaining that the formula refers to itself. Note the cell you were editing and the formula bar contents. This gives you a useful first clue, but it may not identify every cell in a longer dependency loop. If no alert appeared, continue with the workbook-level check.
  2. Open the Circular References list. Select the Formulas tab on the ribbon. In the Formula Auditing group, open Error Checking ▼ and point to or select Circular References. Excel displays the cell address or addresses it has found. Click an address in the list to activate that cell. If the menu item is unavailable, see Troubleshooting below; calculation settings or the current workbook state may affect what Excel reports.
  3. Record the sheet name and cell address. Note which worksheet contains the selected cell and its address, such as Sheet1!A1. When the address is on another worksheet, Excel may switch sheets as you choose it. A listed cell is part of a circular calculation, but it is not always the only cell involved. Avoid assuming that changing the listed cell alone will solve the loop.
  4. Read the formula and identify its references. With the flagged cell selected, inspect the formula bar. Look for a reference to the same cell, or references to other cells that might eventually lead back to it. A direct loop uses the cell’s own address. For example, with cell A1 selected and the formula entered in A1, =A1+1 refers directly to itself. This is a simple example of a circular formula, not a useful calculation to keep in a normal worksheet.
  5. Trace the dependency chain. Select the flagged cell, then choose Formulas > Trace Precedents. Excel draws arrows from cells that feed into the selected formula. Follow those references, selecting relevant cells and tracing their precedents until you find a path back to the original cell. You can also use Trace Dependents to see which cells rely on the selected one. Choose Remove Arrows in the Formula Auditing group when you have finished. For a two-cell loop, with the first formula in A1 and the second in B1, the formulas =B1+1 in A1 and =A1+1 in B1 make each cell depend on the other. Excel may list one or both cells; tracing reveals the complete cycle.
  6. Check formulas on other sheets too. A loop can pass through multiple cells or worksheets, so inspect each reference in the chain rather than looking only at the original sheet. Follow sheet-qualified references such as Summary!B4 to the referenced sheet, then continue tracing. Pay particular attention to totals, subtotals, and input cells: a total formula can accidentally include its own cell if the range extends one row too far.
  7. Correct the cause, then check again. Decide what the calculation is meant to do. Change a reference, shorten a range, or move an intermediate calculation to a separate helper cell so the formula no longer depends on its own result. Return to Formulas > Error Checking ▼ > Circular References and check whether any entries remain. Recheck the dependent results as well; removing the warning does not prove the revised calculation is logically correct.

Excel may keep recalculating as you investigate. If a warning appears, do not dismiss it without checking whether the cycle is intentional. A circular reference can make a result depend on the order or number of calculations, so a plausible-looking number is not proof that the workbook is correct.

Common Mistakes

  • Fixing only the first address shown. The address in the Circular References menu is a starting point, not necessarily the entire cycle. Follow references until you can see how the chain returns to its starting cell.
  • Missing a range that includes its own total. For example, a SUM formula in the bottom row of a column can accidentally include that same row. Check the first and last cells in every range, especially after inserting rows or copying formulas.
  • Confusing a circular reference with an ordinary error. Errors such as #VALUE! or #REF! do not by themselves mean a formula is circular. Use the Circular References menu and inspect dependencies rather than diagnosing from the displayed result alone.
  • Turning on iterative calculation as a quick fix. Iteration allows Excel to calculate some circular formulas repeatedly; it does not repair an accidental reference. Enabling it can hide a mistake and change results, so first confirm that the loop is deliberately designed.
  • Overlooking indirect references. A formula may not mention its own cell but can still loop through another formula, a named range, or another worksheet. Trace the chain all the way back instead of searching only for the cell’s address in its own formula.

Tips

  • Use the status bar as an extra clue. Excel can show a circular-reference notice on the status bar at the bottom of the window, sometimes with a cell address. Treat it as a pointer, not a full map of every formula in the loop; use the Formulas menu to inspect the reference chain.
  • Check names as well as cell addresses. A formula may refer to a defined name rather than a visible address. Open Formulas > Name Manager to inspect names used in the formula and see what ranges or formulas they refer to.
  • Use Evaluate Formula for a difficult chain. Select the formula cell, then choose Formulas > Evaluate Formula. Step through the evaluation to see which references Excel is resolving. This is useful when nested formulas make the dependency path hard to read, though it may not replace tracing across several cells.
  • Keep intentional cycles documented. If a workbook is designed to use iteration, leave a note explaining the purpose and expected behavior. This helps another editor distinguish an intentional calculation from a reference accidentally introduced during a change.
  • Clear audit arrows when finished. Trace arrows are visual aids, not changes to formulas. Use Formulas > Remove Arrows to tidy the worksheet after investigating.

Troubleshooting

The Circular References option is greyed out or the list looks empty. Confirm that the workbook is open in Excel desktop and that you are checking the workbook that contains the formulas. If a warning appeared earlier, note its cell address and inspect it directly. Save the workbook, close it, and reopen it if the menu still seems out of date. Also check whether calculation behavior or workbook protection is limiting what you can inspect.

Excel reports a circular reference, but you cannot see a direct self-reference. The loop is probably indirect. Trace precedents from the flagged cell, follow each relevant formula to its input cells, and continue across worksheets until a reference returns to the starting cell. Check defined names in Name Manager, too. If the arrows are difficult to follow, inspect one formula at a time or use Evaluate Formula to step through its references.

The workbook shows a circular-reference warning every time it opens. Find the flagged address using the Formulas menu and determine whether the cycle is intended. If it is not, correct the formula chain. If it is intentional, verify that iterative calculation is configured as required: on Windows, open File > Options > Formulas, then review Enable iterative calculation, Maximum Iterations, and Maximum Change. These settings control how Excel handles iterative calculations. Do not change them just to suppress a warning without confirming the workbook’s design.

You are using Excel for Mac or Excel for the web. In current desktop Mac editions, look on the Formulas tab for Error Checking and Circular References; settings are under Excel’s preferences or settings, with labels varying by release. Excel for the web has a different ribbon and may not provide the same full auditing or calculation-setting controls. For detailed tracing or iterative-calculation changes, open the file in the desktop app. If the workbook is shared, coordinate changes so another person does not overwrite the correction.

Frequently Asked Questions

What is a circular reference in Excel?
A circular reference exists when a formula relies on its own result. The dependency can be direct, as when a cell refers to itself, or indirect, when a chain of formulas eventually leads back to the starting cell.

Where does Excel show circular references?
On Windows, open Formulas > Error Checking ▼ > Circular References. The list identifies cells Excel has detected as part of a circular calculation. The status bar may also provide a clue.

Can I have more than one circular reference?
Yes. A workbook can contain more than one separate cycle, and one cycle can involve several cells or sheets. Check the menu for available addresses and trace each dependency path; correcting one loop does not guarantee that all others are gone.

Is a circular reference always wrong?
No. Some models deliberately use iterative calculations. However, an accidental cycle can produce misleading or unstable results. Confirm the intended logic before deciding whether to remove it or use iteration.

Should I enable iterative calculation to remove the warning?
Only if the workbook is designed to calculate iteratively. Iteration changes how Excel handles circular formulas; it does not remove the circular dependency. Review the model’s instructions and calculation settings before enabling it.

Can I find a circular reference in Excel for the web?
You may be able to identify a warning or inspect formulas, but the web app does not always expose the same auditing and calculation controls as desktop Excel. Open the workbook in Excel for Windows or Mac when you need to trace the full dependency chain or change iteration settings.

Related

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.