Microsoft Excel Find Circular References: A Clear Guide

To find a circular reference in Microsoft Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References, then click a cell address in the list to jump to it. If the command is unavailable, check the status bar for a circular-reference notice and inspect formulas that refer back to their own cells, directly or through other cells.

What a circular reference means

A circular reference happens when a formula depends on its own result. The dependency can be direct, as when a formula in A1 refers to A1, or indirect, when a chain of formulas eventually leads back to the starting cell. Excel normally warns about a circular reference because it cannot calculate a single answer by following the usual sequence of dependencies.

For a direct example, assume A1 is intended to contain a running total but its formula refers to itself. With the formula entered in A1, =A1+1 creates a circular reference: Excel would need the value of A1 before it could calculate A1.

Indirect loops can be harder to notice. Suppose A1 is meant to add 1 to the value in B1, while B1 is meant to add 1 to A1. With those formulas in A1 and B1 respectively, =B1+1 and =A1+1 form a circular chain. The issue is the dependency loop, not a particular function or formula length.

Not every circular reference is accidental. Some financial or engineering models deliberately use iteration to model a value that depends partly on its own calculated result. But in an ordinary budget, inventory sheet, or sales report, a circular reference is often a misplaced cell reference, a copied formula, or a mistaken total.

Find the reference with Error Checking

Use Excel’s built-in list first. Open the workbook in the desktop version of Excel, select the Formulas tab, choose Error Checking, and point to Circular References. Excel lists one or more cell addresses. Select an address to move to that cell. If more than one address appears, inspect each one; there may be multiple loops or several cells involved in a single loop.

Save a copy of the workbook if it contains important work, especially before making broad formula changes.
Open Formulas > Error Checking > Circular References.
Select a listed cell address and read its formula in the formula bar.
Follow the references in that formula and in the referenced cells until you find where the chain returns to the starting cell.
Correct the reference or, if the loop is deliberate, confirm that iterative calculation is configured appropriately.

The command is a navigation aid, not necessarily a complete explanation of the cause. Excel may show a cell in the loop without describing the whole chain in plain language. If you fix one reference and the circular-reference notice remains, check the list again: another loop may remain, or a different cell may now be the first one Excel reports.

On Excel for Mac, use the Formulas tab and its Error Checking menu to look for Circular References; ribbon wording and placement can vary slightly by release. Excel for the web has a more limited set of desktop diagnostic and calculation controls. If you cannot find the list or need to manage iteration, open the workbook in the desktop app.

Use the status bar and trace tools

When Excel detects a circular reference, the status bar at the bottom of the window may display a circular-reference message and a cell address. Click or note the address, then inspect the formula in that cell. The status bar is useful when the menu list is not obvious, but it may identify only one reference at a time. It does not map every step in a complicated dependency chain.

To follow a suspicious formula, select its cell and choose Formulas > Trace Precedents to display arrows pointing to cells it uses. Then select those cells and inspect their own precedents. Formulas > Trace Dependents shows cells that rely on the selected cell. These arrows can make an indirect loop easier to see across a worksheet. Use Formulas > Remove Arrows to clear the displayed tracing arrows when you are finished.

Tracing is most useful when you already have a likely starting cell. If formulas are spread across multiple sheets, follow the references carefully and switch sheets as needed. A reference to another worksheet can be part of the loop even if the starting formula looks reasonable on its own. Also inspect named ranges used in formulas: a name can conceal which cell or range the calculation depends on.

Check formulas and common causes

Start by asking what the formula is supposed to calculate, then compare that intention with its references. A total formula accidentally included inside its own sum range is a classic cause. For example, if a total is placed in B10, a sum range that includes B10 makes the total depend on itself. Moving the total outside the range or correcting the range boundaries is usually the right repair.

Other frequent causes include:

Copying a formula into the wrong row or column: relative references can shift so a formula points back to its own cell.
Including a result cell in an input range: a subtotal or summary calculation may include the cell where the result is stored.
Changing a formula after inserting rows or columns: references may no longer match the intended layout.
Cross-sheet dependencies: a formula on one sheet refers to a second sheet whose formula eventually points back to the first.
Defined names: a formula may use a name whose underlying reference creates a loop.

To inspect a formula, select the cell and read the formula bar rather than relying only on the displayed value. You can also use Formulas > Show Formulas to display formulas on the worksheet instead of their results. On Windows, Ctrl+` toggles this view; on Mac, the equivalent shortcut is generally Control+`, though keyboard layouts can affect the key. This view helps spot misplaced references but does not itself identify or fix a circular reference.

Repair an accidental circular reference

Choose a correction that reflects the intended calculation, not simply one that makes Excel stop warning. If a total includes its own cell, shorten the referenced range. If a copied formula points to the wrong row, correct the reference or copy the correct formula from a neighboring row. If the loop crosses sheets, decide which sheet should provide the input and which should calculate the result, then remove the backward dependency.

After changing a formula, check the Circular References menu again and verify the resulting values against a simple manual calculation or a known test case. A cell that displays a plausible number is not proof that the loop is gone: Excel may show a previous value or a value produced under iterative calculation. Likewise, deleting a formula can remove the warning while damaging the model. Preserve the intended logic and examine downstream cells that depend on the changed result.

If the workbook contains many formulas, make one targeted correction at a time and recheck. This helps identify which change removed the loop and reduces the risk of introducing a different error. Keep the original copy until totals and dependent calculations have been reviewed.

When to enable iterative calculation

Use iterative calculation only when the circular dependency is intentional and the model’s design requires repeated calculation. In Excel for Microsoft 365 on Windows, select File > Options > Formulas, check Enable iterative calculation, and review Maximum Iterations and Maximum Change. These settings govern when Excel stops recalculating: it stops when it reaches the iteration limit or when successive results change by less than the specified amount.

On Mac, look under Excel > Settings (or Excel > Preferences in some releases) for the calculation options, then find the iterative-calculation controls. The exact labels can vary between versions. In Excel for the web, use the desktop app if the needed iterative settings are unavailable.

Iteration is not a general-purpose fix. It can make a deliberate model work, but results may depend on the starting values, calculation order, maximum iterations, and maximum change setting. If those choices are not understood, a workbook can produce numbers that look stable but do not represent the intended answer. Document why the loop exists, test the model with known inputs, and make sure other people using the workbook know that iteration is enabled.

Common misunderstandings and troubleshooting

A circular reference is not the same thing as a formula error such as #REF!. A #REF! result usually means a formula contains an invalid cell reference, while a circular reference is a dependency loop. Fixing a broken reference may be necessary, but the two issues are diagnosed differently.

Another misconception is that every formula containing its own row or sheet name is circular. What matters is whether the formula’s actual dependency chain returns to its own result cell. Conversely, a formula can create a loop without mentioning its own address directly, because another referenced formula may lead back to it.

If Excel continues to report a loop after a repair, revisit Formulas > Error Checking > Circular References, inspect any newly listed address, and trace its dependencies. Confirm that you are checking the same workbook and sheet you edited. If a workbook was opened from another application or an online location, open it in desktop Excel and allow calculation to complete before concluding that the warning is gone.

Frequently Asked Questions

Where is the Circular References command in Excel?
Select Formulas > Error Checking > Circular References in desktop Excel for Windows. Click a cell address in the menu to go to that cell. On Mac, look for the same command on the Formulas tab; Excel for the web may not provide the same diagnostic controls.

Why can’t I see Circular References in the menu?
The submenu may appear only when Excel has detected a circular reference in the workbook. Check the status bar and inspect any recent formula edits. If you are using Excel for the web, open the workbook in the desktop app to access the fuller set of formula-checking controls.

Can I find a circular reference without changing the formula?
Yes. Use the Circular References list, check the status bar, and trace precedents and dependents. These steps help locate the dependency path without editing formulas. Save a copy before making repairs if you want a safe version to return to.

Should I always remove a circular reference?
No. Remove accidental loops, but some models intentionally use iterative calculation. Before enabling iteration, confirm that the loop is part of the model’s design and understand the maximum-iteration and maximum-change settings.

Does enabling iterative calculation fix the formula?
It permits Excel to recalculate intentional circular formulas repeatedly; it does not correct a mistaken cell reference. If the loop is accidental, repair the formula instead. Otherwise, iteration can conceal a modeling mistake rather than solve it.

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.