How to Undo a Circular Reference in Excel Step by Step

How-to basics · Updated November 21, 2026

To undo a circular reference in Excel, find the cell or cells in the loop, then edit the formula so it no longer depends on its own result. In Microsoft Excel for Microsoft 365 on Windows, start with Formulas > Error Checking > Circular References and select a listed cell. You can use Ctrl+Z if the circular formula was your most recent change, but editing the formula is usually the safer fix when you need to preserve other work.

A circular reference happens when a formula refers to its own cell, either directly or through a chain of other formulas. Excel may warn you, show a circular reference in the status bar, or calculate an unintended result if iterative calculation is enabled. The steps below help you locate the loop, decide what the formula should calculate, and confirm the workbook is working afterward.

What You’ll Need

  • The workbook containing the circular reference. If it is important, save a copy first with File > Save As.
  • Access to Excel’s formulas and formula-auditing tools. The instructions focus on Microsoft 365 for Windows; Mac and web differences are noted where relevant.
  • A clear idea of what the formula is meant to calculate. Excel can point to a circular cell, but it cannot decide the intended business logic for you.

Step-by-Step

  1. Save a working copy. Choose File > Save As and save a duplicate before making changes, especially if the workbook contains many linked sheets or important calculations. Editing a formula is usually reversible with Ctrl+Z on Windows or Command+Z on Mac, but a saved copy gives you a reliable way back if you change the wrong part of a complex model. If you are collaborating, make sure you are editing the intended copy and not a shared original.
  2. Check whether Excel is reporting a circular reference. Look at the status bar at the bottom of the Excel window for a circular reference notice. You can also open the Formulas tab and choose Error Checking > Circular References. When Excel detects a loop, the menu can list a cell address; select it to jump to that cell. If the menu lists more than one address, work through the listed cells rather than assuming the first one is the only problem.
  3. Inspect the selected cell and its formula. Click the cell and read the formula bar. A direct circular reference is easy to spot: the formula refers to its own address. For example, if the layout is a single cell C2 containing a value that is supposed to increase itself, the formula =C2+1 refers to C2 while calculating C2. That is a direct loop. Do not replace it automatically until you know what value or calculation the cell is supposed to represent.
  4. Follow indirect references when the cell does not refer to itself. A loop can pass through several cells. For instance, A1 may depend on B1, while B1 depends on C1 and C1 depends on A1. Select the cells involved and use Formulas > Trace Precedents or Formulas > Trace Dependents to draw arrows showing formula relationships. Double-click an arrow, where available, to move between related cells. For references on other worksheets, inspect the formulas on those sheets as well; a chain can cross sheet boundaries.
  5. Work out what the formula should include. Compare the formula with the layout and the intended calculation. A common mistake is placing a total inside the range that it totals. For example, if B2:B10 contains a list of amounts and B10 is also meant to hold their total, a formula in B10 that sums B2:B10 includes itself and creates a loop. If the intended inputs are in B2:B9 and the total belongs in B10, use =SUM(B2:B9) in B10. The result will be the sum of the values in B2 through B9.
  6. Make the smallest appropriate formula change. You might exclude the total cell from a range, correct a mistaken cell reference, or move the result formula to a cell outside its input range. If a formula is supposed to calculate from a previous period, reference the previous period rather than its own output. Avoid deleting several cells or rewriting a whole sheet just to silence the warning; a small, explained change is easier to verify and less likely to break dependent calculations.
  7. Check for other cells in the loop. After editing, return to Formulas > Error Checking > Circular References. If Excel still lists an address, select it and inspect its formula. A circular chain may have more than one formula that needs correction. Also check related sheets and named ranges if the visible formula looks reasonable but the warning remains. A name used in a formula can refer to a range that leads back to the formula cell.
  8. Confirm that the workbook calculates as intended. Check the changed cell and any totals, summaries, or charts that rely on it. Compare the result with a simple hand calculation or a known example. If Excel no longer reports a loop but the number is wrong, the circular reference may be fixed while the formula logic is still incorrect. Check the input range, relative and absolute references, and whether the total is in the intended location.
  9. Save the corrected workbook. Once the warning has cleared and the results make sense, save with Ctrl+S on Windows or Command+S on Mac. Keep the backup until you have checked the important outputs. If you edited a shared workbook, confirm that your changes synced and that collaborators can see the corrected version.

Mac and web notes: On Excel for Mac, look for the Error Checking and Circular References controls on the Formulas tab; ribbon layout can vary slightly by release. Excel settings are under Excel > Settings or Excel > Preferences, then Calculation. In Excel for the web, inspect the affected formula and use available formula-auditing controls; if you cannot find the circular-reference locator or calculation settings, open the workbook in the desktop app to follow the Windows or Mac instructions.

Common Mistakes

  • Deleting the formula before noting its purpose. Removing a formula may stop the warning, but it can also leave a total or dependent calculation blank. Record what the cell is meant to calculate before changing it.
  • Changing the wrong cell in a circular chain. The first listed address is a place to investigate, not proof that it is the only cause. Follow the dependencies and check every cell Excel identifies.
  • Excluding a valid input by mistake. Shortening a sum range can fix a loop but omit real data. Confirm where the inputs end and where the result belongs before changing the range.
  • Turning on iterative calculation to hide the alert. Iteration lets Excel recalculate circular formulas repeatedly under chosen limits. It does not remove the loop, and it can change results throughout the workbook. Use it only when the model intentionally relies on iterative calculations and you understand its settings.
  • Assuming Ctrl+Z will always restore the original workbook. Undo works for recent editable actions, but after many changes it may not be practical to step back safely. A backup is a more dependable recovery option.

Tips

  • Keep input cells, calculation cells, and final outputs in separate areas. A total placed below its input range is less likely to include itself accidentally.
  • Use descriptive labels next to formulas, such as “Monthly inputs” and “Total,” so range boundaries are easy to check later.
  • When correcting a formula, change one thing at a time and check the result before continuing. This makes it easier to identify which change fixed the loop.
  • Use Formulas > Show Formulas if you need to review many formulas on a sheet. Choose it again to return to normal calculated values.
  • Keep iterative calculation off unless the workbook deliberately needs it. On Windows, check File > Options > Formulas > Calculation options. Turning it off can reveal a circular reference that was previously being calculated iteratively, but it does not repair the underlying formulas.

Troubleshooting

The Circular References menu is empty, but a warning remains. Recalculate with Formulas > Calculate Now, then check the status bar and reopen the menu. Confirm that you are looking at the workbook that contains the warning. If the workbook has recently been edited or opened in the web app, save it and open it in desktop Excel to inspect the formulas and calculation settings.

The same address keeps appearing after you edit it. The formula may still lead back to itself through another cell, or a named range may point to a range that includes the formula cell. Use Trace Precedents and Trace Dependents, inspect each link, and check Formulas > Name Manager for names used by the formula. Correct the link that closes the loop rather than repeatedly changing the visible cell.

The warning disappeared, but the result is zero or otherwise unexpected. Recheck the formula’s range and the cells it depends on. Make sure the intended input cells contain numbers rather than text, and verify that you did not remove a required row or reference the wrong sheet. Compare the result with a small manual calculation. A cleared warning only means Excel no longer detects that circular loop; it does not guarantee the replacement formula is correct.

You intentionally need a circular calculation. Some financial or engineering models use iterative calculations by design. In that case, do not remove the loop just to clear a message. Confirm the workbook’s instructions, then review the iteration limit and maximum change in Excel’s calculation settings with the model owner. Changing those settings can affect other formulas, so test a copy and check key outputs before using the result.

Frequently Asked Questions

What does “circular reference” mean in Excel?
It means a formula depends on its own result, either directly or through other formulas. The dependency eventually returns to the formula cell, creating a loop.

Can I undo a circular reference with Ctrl+Z?
Yes, if the formula that caused it was your most recent action, use Ctrl+Z on Windows or Command+Z on Mac. If you have made other changes since then, locate and correct the formula instead, or restore your saved backup.

Why can’t I find the Circular References command?
In Windows desktop Excel, check Formulas > Error Checking > Circular References. The command may not show any addresses when Excel does not currently detect a loop. On Mac or Excel for the web, the ribbon layout and available controls may differ; opening the file in desktop Excel can help you locate the formula.

Does enabling iterative calculation fix a circular reference?
No. It allows Excel to calculate a circular model repeatedly using iteration settings, but the circular dependency remains. Enable it only when the workbook is designed to use that method.

Will fixing the circular reference change other results?
It can. Formulas that depend on the corrected cell may recalculate, and their outputs may change. Review related totals and reports before relying on the workbook.

Can a circular reference involve different worksheets?
Yes. A formula on one sheet can depend on a cell on another sheet that eventually points back to the first formula. Follow the precedent and dependent cells across every sheet in the chain.

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.