How to Find and Clear a Circular Reference in Excel

How-to basics · Updated November 18, 2026

To clear a circular reference in Excel, find the flagged cell using Formulas > Error Checking > Circular References, then edit its formula so it no longer depends on its own result, directly or through another cell. Save a copy first if the workbook is important. Do not switch on iterative calculation just to hide a warning; use it only when the circular calculation is intentional.

What You’ll Need

  • The workbook open in the Excel desktop app for Microsoft 365 on Windows. The instructions below use the Windows ribbon; Mac differences are noted where relevant.
  • Permission to edit the workbook and, if it contains important work, a saved backup or a duplicate copy to work in.
  • A basic understanding of what the affected calculation is meant to return. This helps you repair the logic rather than simply deleting a formula.

Step-by-Step

  1. Save a working copy. Use File > Save As to create a copy before changing formulas in a complex workbook. If you discover that a repair affects downstream totals, you can compare with the original. If the workbook is stored on OneDrive or SharePoint, version history may also be available, but do not rely on it instead of saving a deliberate backup.
  2. Note when Excel reports the problem. A circular-reference warning may appear when you enter or change a formula, or when you open or calculate a workbook. Read the warning and note the sheet or cell address if Excel provides one. The warning means a formula depends on its own result, either by referring to its own cell or by relying on a chain of formulas that eventually returns to it. It does not necessarily mean the entire workbook is broken.
  3. Open Excel’s circular-reference list. On Windows, select the Formulas tab, open Error Checking in the Formula Auditing group, and point to or select Circular References. If Excel has identified a circular reference, the submenu lists a cell address; select an address to go to that cell. If there is more than one entry, record each address. The list can identify a cell on the current sheet, but a cell in a different sheet may need to be checked separately.
  4. Check the status bar and repeat on other sheets. Excel may also show Circular References and a cell address on the status bar at the bottom of the window. If the list or status bar points to a cell, note its sheet name and address. Then inspect the other sheets using the same Error Checking menu. A workbook can contain more than one loop, and Excel may reveal another one after you fix the first. If neither place shows a useful address, continue with the formula-auditing checks below.
  5. Inspect the flagged formula and its inputs. Select the cell and read the formula in the formula bar. Look for a reference to the same cell, such as a total formula that includes the cell containing that total. Then trace the referenced cells: select each important input and use the formula bar to see how it is calculated. On Windows, Formulas > Trace Precedents displays arrows to cells used by the selected formula, while Trace Dependents shows cells that use it. Use Remove Arrows in the same area when you are finished. These arrows help reveal indirect loops, although they may not show every relationship clearly when formulas cross sheets or use names.
  6. Decide what the calculation should do. Write down the intended inputs and output before editing. For example, if a quantity is in A2, a unit price is in B2, and the revenue result belongs in C2, the layout should keep the result separate from its inputs. With that cell layout, the formula in C2 is =A2*B2. If A2 is 3 and B2 is 5, the result is 15. A formula in C2 that refers to C2 instead would make the result depend on itself. Keeping input cells, intermediate calculations, and final totals separate is often the simplest way to prevent a repeat.
  7. Replace the circular logic with a non-circular formula. Edit the flagged cell or another cell in the loop so that the calculation refers only to the intended inputs. For example, suppose D2 contains an amount of 10 and E2 is meant to add 5. With D2 as the input and E2 as the result, enter =D2+5 in E2; it returns 15. Do not put a formula such as =E2+D2 in E2, because it includes its own result cell. For totals, place the total outside the range being added. If values are in B2:B10, put the total in B11 and enter =SUM(B2:B10) there; the result is the sum of the nine cells without including B11 itself.
  8. Check formulas that were filled or copied. If the loop appeared after copying a formula, inspect the cell references carefully. Relative references change as a formula is filled, so a copied formula can begin including its own row or column. Check whether a range accidentally extends into the result cell, whether a total was placed inside its own sum range, or whether a reference should point to a fixed input cell. If you change a reference, confirm that the new formula still matches the workbook’s intended calculation.
  9. Recalculate and check for remaining loops. Press F9 to recalculate the workbook, then reopen Formulas > Error Checking > Circular References. Fix any remaining listed cells one at a time and check again. If calculation is set to Manual, use Formulas > Calculation Options > Automatic when appropriate, or recalculate after each change. On a Mac, the F9 shortcut may require the Fn key depending on keyboard settings; use the Formulas ribbon if the shortcut behaves differently.
  10. Verify the repaired result. Compare the changed output with a small hand calculation or a known-good value, and inspect important totals or dependent cells. Check that formulas still fill down correctly and that no intended input was overwritten. Save the workbook once the results are correct. If a deliberate circular calculation is part of the model, follow the iterative-calculation guidance in the FAQ rather than treating the loop as an ordinary mistake.

Common Mistakes

Deleting the formula without understanding it. Removing the flagged formula will often remove the warning, but it may also remove an important total or calculation. First identify what the cell is meant to calculate and where its inputs should come from. Then alter the formula structure or move the result to a separate cell.

Putting a total inside the range it totals. If a SUM formula is entered in a cell that is part of its own referenced range, Excel sees a direct loop. A common layout error is placing a total at the bottom of a column and including that row in the selected range. Put the total outside the data range, or carefully adjust the range so it includes only the values that should be added.

Fixing only the first warning. Excel may display one circular reference first even when other loops exist. After each repair, check the Circular References submenu again. Also check separate sheets; fixing one loop does not prove every circular dependency is gone.

Turning on iterative calculation as a shortcut. Iterative calculation allows Excel to recalculate circular formulas repeatedly. It can make a warning disappear or allow a designed model to calculate, but it does not correct an accidental reference. It can also change workbook results and may affect other circular formulas, so do not enable it unless you understand the model and its required settings.

Tips

  • Use separate areas or sheets for inputs, intermediate calculations, and outputs. This makes it easier to see whether an output has accidentally become one of its own inputs.
  • Use clear labels beside important formula cells. A label such as Total or Tax rate can make an incorrect reference easier to spot when you inspect a formula.
  • When tracing a complex model, follow references in small steps and write down the chain, such as A1 to B4 to C7 to A1. This makes an indirect loop easier to explain and repair.
  • If the formula contains a named range, inspect it using Formulas > Name Manager. A name can refer to a range that includes the formula cell, creating a loop that is not obvious from the cell formula alone.
  • After correcting a formula, test both a typical input and a boundary case, such as a blank or zero input, if those values are possible in the workbook. This helps catch a repair that removes the circular reference but produces an unintended result.
  • Do not confuse a circular reference with a formula error such as #VALUE! or #REF!. Those errors have different causes. Use the Circular References menu to confirm that a loop is actually present.

Troubleshooting

The Circular References menu is unavailable or empty. Recalculate with F9 and check the status bar. Confirm that you are looking at the workbook and sheet that produced the warning, especially if more than one workbook is open. If the issue persists, inspect recently edited formulas, named ranges in Name Manager, and totals whose ranges may include their own result cells. A warning may also refer to a different sheet than the one currently visible.

Excel points to a cell, but its formula does not refer to itself. The reference may be indirect. Trace the formula’s precedents, then inspect the formulas in those cells and continue until you find a path back to the original cell. For example, one cell may refer to a second cell while that second cell refers to a third that eventually refers to the first. Also check names and formulas on other sheets. Repair one link in the chain by changing the calculation to use a true input rather than a dependent output.

The warning returns after you fix it. Another loop may remain, or a formula may have been copied back into the affected range. Reopen the circular-reference list after recalculation and inspect the newly listed address. If a formula is copied by a table, fill handle, or another user, check the source formula and the table’s calculated-column behavior so the bad reference is not reintroduced.

The formula now calculates, but the answer looks wrong. Removing a loop does not guarantee the replacement logic is correct. Compare the formula’s references with the intended inputs, check for omitted rows or shifted references, and test a small example with values you can calculate independently. If the workbook uses Manual calculation, recalculate it before judging the displayed result.

The circular calculation is intentional. Some models deliberately use iteration, for example when a calculation depends on a value that is itself calculated within the model. Confirm the design and required tolerance with the workbook owner before changing settings. On Windows, open File > Options > Formulas, select Enable iterative calculation, and set Maximum Iterations and Maximum Change to values specified for that model. On Mac, open Excel > Settings (or Preferences, depending on the version) and look under Calculation for iterative calculation settings. Excel for the web has fewer calculation controls; if you need to change iteration settings, open the workbook in the desktop app. Enabling iteration changes calculation behavior, so record the original setting and verify the results afterward.

Frequently Asked Questions

What is a circular reference in Excel?
A circular reference occurs when a formula refers to its own cell, either directly or through other formulas. The result depends on itself, so Excel cannot calculate it as an ordinary one-way formula unless iterative calculation is deliberately enabled.

How do I find a circular reference quickly?
In Excel for Microsoft 365 on Windows, choose Formulas > Error Checking > Circular References and select a listed cell address. You can also look for a circular-reference message and cell address on the status bar. Check again after each repair because more loops may remain.

Can I remove a circular reference without deleting the formula?
Usually. Change the formula so it uses the right input cells, or move the result to a cell outside the range it totals. The aim is to preserve the intended calculation while breaking the path that leads back to the formula cell.

Should I enable iterative calculation?
Only if the circular relationship is intentional and the model specifies appropriate iteration settings. For an accidental loop, fix the formula instead. Iteration can affect workbook-wide calculations and produce different results from a non-circular model.

Why does Excel still show a circular reference after I fixed one?
The workbook may contain another loop, including one on a different sheet, or a copied formula may have restored the bad reference. Recalculate, check the Circular References menu again, and trace each newly identified cell until the list is clear.

Do the steps differ on Mac or Excel for the web?
The Windows ribbon path is described above. Excel for Mac has a Formulas tab and calculation settings in Excel Settings or Preferences, though some labels and shortcut behavior vary by version. Excel for the web may not expose every desktop calculation control, so use the desktop app when you need to change iterative-calculation 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.