Circular References in Excel: How to Find and Fix Them

Errors & fixes · Updated November 18, 2026

A circular reference in Excel is a formula that refers, directly or through other formulas, back to its own cell. In Microsoft 365 for Windows, find one by selecting Formulas > Error Checking > Circular References, then inspect the listed cell and the formulas connected to it.

What a circular reference means

Every formula needs to calculate from inputs. A circular reference occurs when a formula’s result depends on itself, so Excel cannot calculate it in the usual one-pass way. The loop can be obvious, as when a cell refers to itself, or indirect, when two or more formulas depend on one another in a chain.

For a direct example, assume cell A1 contains the formula =A1+1. A1 needs its current value to work out its next value, but that value is the result Excel is trying to calculate. For an indirect loop, assume B2 and C2 are both calculated cells. If B2 contains =C2+1 and C2 contains =B2+1, each cell needs the other before either can be resolved.

Excel normally warns when it detects a circular reference. Depending on the workbook’s calculation settings and the circumstances, the affected cell may display 0, a previous calculated value, or another result that should not be trusted until the loop is understood. A circular reference is not the same as an ordinary formula error such as #DIV/0!; it describes a dependency problem in the calculation model.

How to find circular references

On Excel for Microsoft 365 for Windows, open the Formulas tab and choose Error Checking > Circular References. If Excel has identified one, the menu lists a cell address. Select an address to move to that cell, then inspect its formula bar. If the menu lists more than one address, check each one rather than assuming the first is the only problem.

  1. Select the address shown under Formulas > Error Checking > Circular References.
  2. Read the selected cell’s formula and note each referenced cell or range.
  3. Follow the references through the formulas they depend on until you find the path back to the original cell.
  4. Decide whether the loop is accidental or deliberately part of the model before changing calculation settings.

The status bar may also report a circular reference and show a cell address. This is a useful clue, but it does not explain the whole dependency chain. If the warning appears after you edit a formula, undoing the change with Ctrl+Z on Windows can help confirm which edit introduced the loop.

In Excel for Mac, use the Formulas tab’s error-checking controls to look for Circular References; ribbon labels and layout can differ from Windows. Excel for the web has a more limited auditing and calculation-setting workflow. For a complicated loop, open the workbook in the desktop app to use the full set of formula-auditing tools.

How to read the dependency loop

A circular-reference warning identifies a cell involved in a loop, not necessarily the one formula that should be changed. Work through the dependency path one formula at a time. A formula can point to another cell, which points to a third, and eventually lead back to the starting cell. Look especially for a calculation that accidentally includes its own output or a cell that is meant to be an input but is actually calculated from the output.

For instance, if a formula in D5 uses E5, check whether E5 uses another cell that ultimately uses D5. It is often helpful to write the chain down or temporarily inspect each formula in the formula bar. Avoid changing several formulas at once: a small, deliberate correction makes it easier to see whether the loop has been removed without changing the intended results.

Excel’s Trace Precedents and Trace Dependents commands can help show relationships between cells. On Windows, find them on the Formulas tab in the Formula Auditing group. Arrows show direct relationships, not a complete explanation of every multi-step calculation, so follow the arrows and repeat the tracing as needed. Remove arrows with Remove Arrows when they clutter the sheet.

Common causes and a concrete example

A frequent cause is a total formula that includes the cell containing the total. Assume B2:B9 contain individual values, and the total formula is entered in B10. The formula =SUM(B2:B10) includes B10 itself, creating a direct circular reference. The intended range is the input cells only, so in B10 use =SUM(B2:B9). That corrected total adds the eight values in B2 through B9.

Other common causes include:

  • Copying a formula and letting a relative reference shift into the formula’s own row or column.
  • Including a subtotal or grand-total cell in the range it is supposed to summarize.
  • Changing an input cell into a formula when other formulas already depend on it.
  • Using formulas across multiple sheets where a chain of references returns to the starting sheet.
  • Editing a formula in a template without checking how its references behave when filled down or across.

For the total example, a good layout separates source data from the result. If B2:B10 contain the values, place the total in B11 and use =SUM(B2:B10) there. With values 2, 4, 6, 8, 10, 12, 14, 16, and 18 in B2:B10, the result in B11 is 90. The same formula text that caused a loop when placed in B10 is safe in B11 because the result cell is outside the summed range.

How to fix an accidental loop

First decide what the formula is supposed to calculate. Then remove the unwanted dependency rather than trying to silence the warning. Common repairs include narrowing a range, moving a total outside its source range, restoring a value that should be an input, or changing a reference to the correct cell.

  1. Save a copy of the workbook before making broad changes, especially if it contains linked sheets or important reporting formulas.
  2. Use the Circular References menu to locate a cell, then trace the formulas that lead back to it.
  3. Compare the actual formula with the intended calculation and identify the mistaken reference or range.
  4. Correct one formula and check whether the warning disappears and dependent results still make sense.
  5. Test the workbook with known or simple input values, and review nearby totals and summaries for unintended changes.

Do not simply replace a displayed 0 or other unexpected value with a typed number unless that is genuinely the intended input. Doing so may hide the cause while leaving other formulas dependent on a broken calculation. Similarly, deleting a formula can remove a loop but also discard an important part of the model.

When iterative calculation is appropriate

Some specialist models deliberately use circular formulas and rely on repeated calculation. Excel calls this iterative calculation. Rather than calculating a circular formula just once, Excel recalculates the dependent formulas repeatedly, stopping when it reaches the configured maximum number of iterations or when the change between results is small enough. The result therefore depends on the model, starting values, and iteration settings.

To enable it in Microsoft 365 for Windows, choose File > Options > Formulas, select Enable iterative calculation, and review Maximum Iterations and Maximum Change. These controls determine when Excel stops iterating; they do not prove that a model is correct. A result can stop changing enough to meet the threshold and still be inappropriate for the purpose of the workbook.

Use iteration only when the workbook is designed for it and you understand how its formulas converge. Before enabling it, check whether the calculation can be rewritten to use explicit inputs or a separate calculation step. If you do enable it, document why, test the model with reasonable inputs, and verify that changing iteration settings does not produce misleading results. In Excel for Mac, look under Excel > Settings > Calculation for iterative-calculation controls; the labels and layout may vary by release.

Circular references versus other formula errors

A circular reference is about the direction of a formula dependency: the calculation leads back to itself. By contrast, #REF! generally indicates an invalid cell reference, often because a referenced cell or range was deleted. #DIV/0! indicates division by zero, while #VALUE! commonly indicates an unsuitable value or argument. These errors need different fixes.

One workbook can contain both a circular reference and other formula errors. Correcting the loop does not guarantee that every cell will calculate correctly, so review the affected outputs afterward. If Excel shows a circular-reference warning but the menu is empty, check the active sheet, recalculate the workbook, and inspect other sheets or formulas that may be part of a longer dependency chain.

Ways to prevent circular references

Clear worksheet structure is the simplest prevention. Keep inputs, intermediate calculations, and final totals in distinct areas or columns, and avoid placing a result inside the range it summarizes. Use descriptive labels so it is obvious which cells are inputs and which contain formulas.

When filling formulas, inspect the first and last rows to confirm that references shift as intended. Use absolute references where a reference should remain fixed when copied, but do not use them as a substitute for understanding the calculation: an absolute reference can still create a circular dependency. Before adding a formula to a workbook used by other people, check the Circular References menu and validate key outputs with a small, independently calculated example.

Frequently Asked Questions

How do I find a circular reference in Excel?
In Excel for Microsoft 365 on Windows, select Formulas > Error Checking > Circular References. Choose a listed cell address, then inspect its formula and follow its references until you find the loop.

Why does Excel show a circular reference warning?
Excel detected a formula that depends on its own result, either directly or through other cells. A common example is a total formula that includes the cell where the total is entered.

Is a circular reference always wrong?
No. Some calculation models deliberately use iterative calculation, but an accidental loop is usually a formula or range mistake. Confirm the workbook is designed for iteration before enabling it.

What happens if I ignore a circular reference?
Excel may show an unexpected or stale result, and dependent calculations may be unreliable. Do not rely on the affected outputs until you have corrected the loop or confirmed that the workbook intentionally uses iterative calculation.

How do I remove a circular reference without changing the result?
Identify the intended calculation first, then correct the reference or range that creates the loop. For a total, put the result outside the source range and sum only the input cells; afterward, compare the result with a simple independent check.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.