To get around a circular reference in Excel, first find the cell or formulas creating the loop, then either rewrite the formulas so they no longer depend on themselves or enable iterative calculation if the loop is intentional. Before changing calculation settings, save a copy of the workbook and decide which of those two situations applies.
A circular reference occurs when a formula depends on its own result, either directly or through other cells. Excel normally warns about this because the formula may not have a stable answer. Iterative calculation can handle some intentional loops, but it is not a universal repair: an accidental reference can still produce a misleading result.
What You’ll Need
- Excel for Microsoft 365 on Windows, with the workbook open in the desktop app.
- A saved copy of the workbook, especially before changing calculation settings.
- For an intentional circular model, a clear idea of what result should settle on and an acceptable precision for that result.
You do not need an add-in or a special formula to locate a circular reference. If you are using Excel for the web, open the workbook in the desktop app to configure iterative calculation; the setting may not be available in the web interface.
Step-by-Step
-
Save a working copy. Choose File > Save As and save a separate version before editing formulas or calculation options. This gives you a safe way to compare results or undo a broad change. Excel’s iterative calculation setting affects circular formulas in the workbook, so do not switch it on just to silence a warning without checking the model.
-
Find the circular reference. Open the Formulas tab, then choose Error Checking > Circular References. Select a cell in the list to jump to it. The menu can identify a cell in the loop, but you may need to follow its references to see the full chain. If the command is unavailable or the list is empty, first select a worksheet cell and check whether Excel is showing a circular-reference warning in the status bar.
-
Trace the formula chain. Read the formula in the selected cell and note every referenced cell. Use Formulas > Trace Precedents to see which cells feed into it, and Trace Dependents to see which cells rely on it. Repeat this on the linked cells until you find the reference that returns to the original cell. A loop may cross several sheets, so check sheet names in formulas rather than assuming all dependencies are on the active sheet.
-
Choose whether the loop is intended. If a formula accidentally includes its own cell in a range, or two cells refer to each other because of a copied formula, remove or correct that dependency. If the calculation is designed to refine a value repeatedly until it settles, such as a model where one calculated amount feeds back into another, iterative calculation may be appropriate. Confirm the intended logic with the workbook’s owner before changing a model you did not create.
-
For an accidental loop, correct the cell references. A common mistake is a total formula entered inside the range it totals. For example, if a total belongs in B21 and the values are in B2:B20, keep the total range limited to those value cells. If a copied formula refers to the wrong row or column, edit the reference and then check the neighboring formulas. Once the dependency is removed, Excel should recalculate without the circular-reference warning.
-
For an intentional loop, turn on iterative calculation. In Excel for Microsoft 365 on Windows, choose File > Options > Formulas. Under Calculation options, select Enable iterative calculation. Set Maximum Iterations and Maximum Change, then select OK. Maximum Iterations limits how many calculation passes Excel makes; Maximum Change sets how small the change between passes must become before Excel stops. Lower maximum change generally asks for a more precise result, but can take more iterations.
-
Set the limits with a small, known example. Suppose A2 contains the fixed value 100, B2 is the base plus a feedback amount, and C2 is 10% of B2. With A2 containing 100, enter this in B2:
=A2+C2. With B2 calculated from A2 and C2, enter this in C2:=B2*10%. These formulas deliberately create a loop: B2 depends on C2, while C2 depends on B2. With iterative calculation enabled and enough iterations, the values settle near B2 = 111.11 and C2 = 11.11. The exact displayed result depends on the iteration and change limits. -
Check the result and save. Confirm the result is plausible, compare it with an independent calculation if possible, and check that unrelated workbook results have not changed unexpectedly. Save, close, and reopen the copy to verify that the workbook retains the intended calculation behavior. If the values change substantially when you recalculate, investigate the model rather than simply increasing the iteration limit.
For the example above, you can also remove the loop mathematically when the feedback rate is fixed. With A2 containing 100 and the feedback rate fixed at 10%, put this in B2: =A2/(1-10%). It returns approximately 111.11, and the corresponding 10% amount is approximately 11.11. This direct formula avoids iterative calculation. Use this approach only when the relationship really follows that fixed-rate equation; a different model may need a different algebraic solution.
On a Mac, the calculation controls are generally under Excel > Settings (or Preferences) > Calculation; wording and layout can vary by release. The Windows File > Options path does not apply on Mac. If you cannot find the setting in your version, use Excel’s Help search for iterative calculation. Excel for the web may recalculate a workbook that already uses iteration, but use the desktop app to configure the calculation options.
Common Mistakes
-
Enabling iteration to hide an accidental error. Iteration can make a warning disappear without making the formula correct. Identify the dependency chain first, then decide whether feedback is part of the design.
-
Setting Maximum Change too loosely. A large tolerance can stop calculation while the result is still noticeably changing. Set a tolerance that fits the scale and precision your workbook needs, and verify the result rather than relying on the setting alone.
-
Assuming more iterations always fix the model. Extra passes do not guarantee convergence. A loop can oscillate, diverge, or settle at a value that is mathematically possible but not meaningful for the task.
-
Forgetting that other circular formulas may be affected. Once iteration is enabled, other circular references in that workbook can also calculate iteratively. Review the Circular References list and test sheets that contain separate calculations.
-
Using a formula that includes its own cell in a total. A total placed within the range being summed is a frequent source of circular references. Check the start and end of each range, especially after inserting rows or copying formulas.
Tips
-
Keep a short note in the workbook explaining why an intentional circular reference exists, which cells form the loop, and what iteration limits were chosen. This helps the next person distinguish a deliberate model from an error.
-
Test a circular model with simple inputs whose expected result you can work out independently. If changing an input produces an implausible jump, the loop may not be stable or the formula logic may be wrong.
-
Use clear input cells for fixed assumptions, separate from calculated cells. This makes it easier to trace dependencies and prevents a formula from accidentally referring to its own output.
-
If the relationship can be solved directly, prefer a non-circular formula. It is usually easier to audit, less dependent on calculation settings, and more predictable when another person opens the workbook.
-
When reviewing a complex workbook, inspect formulas across all relevant sheets and named ranges. A loop can travel through a named formula or a cell on another worksheet, so the visible formula alone may not tell the whole story.
Troubleshooting
The Circular References menu does not show the cell I expect. Excel may show one location at a time, and the first listed cell is not necessarily the only cause. Select the listed cell, trace its precedents, and follow the chain. Check other worksheets and defined names as well. After correcting one loop, revisit Formulas > Error Checking > Circular References to see whether another remains.
The warning returns after I edit a formula. The edit may have recreated the dependency, or another formula may still lead back to the same cell. Undo the last edit if appropriate, inspect the edited range boundaries, then trace precedents again. If you copied a formula, compare its relative and absolute references with a nearby formula that works.
Iteration is enabled, but the result keeps changing or looks wrong. Check whether the values settle as calculation proceeds and whether the model is designed to converge. Review Maximum Iterations and Maximum Change, but do not simply raise both values until the result looks acceptable. Confirm each formula’s intended relationship, test with controlled inputs, and compare against an independent calculation. If it does not converge reliably, rewrite the model to avoid the loop.
The setting is missing or the workbook behaves differently on another computer. Confirm that you are in the desktop version of Excel and that the workbook was saved after changing the calculation options. Mac menu labels differ from Windows, and Excel for the web has fewer calculation controls. Ask the workbook’s owner which Excel version and settings the model requires, then verify the calculation in that environment.
Other formulas changed after iteration was enabled. Review the workbook’s other circular references and compare the affected outputs with your saved copy. If those loops were not intentional, correct them or turn off iterative calculation after resolving the intended model another way. Avoid distributing a workbook until you understand why its results changed.
Frequently Asked Questions
Is a circular reference always an error?
No. It is an error when the dependency is accidental or the calculation cannot produce a useful result. Some models deliberately feed a calculated value back into another formula; those may use iterative calculation if they converge and the results are validated.
Can I remove a circular reference without changing the result?
Sometimes. If the circular relationship can be rearranged algebraically, replace it with a direct formula. The fixed 10% example above can be written without a loop. Other circular models may rely on more complicated logic, so do not substitute a simplified formula unless it represents the same relationship.
Why does Excel say there is a circular reference when I cannot see one?
The dependency may pass through another cell, worksheet, or defined name. It can also be hidden in a formula copied into a range or in a cell that is not currently visible. Use the Circular References list and trace the references one link at a time.
What should I use for Maximum Iterations and Maximum Change?
There is no single suitable pair for every workbook. Choose a change tolerance that matches the precision the result needs and a maximum iteration count that allows a stable model to settle. Test representative inputs and confirm the output independently before relying on it.
Will iterative calculation work in Excel for the web?
Use Excel for the desktop to enable or adjust iterative calculation. A workbook configured in desktop Excel may show calculated values in the web app, but the available controls can differ. For important models, check results in the desktop version and environment the workbook is designed for.
Related
- Create a Table in Excel With a Title: Step-by-Step
- Excel Table Design: Create and Format a Table
- How to Add Clickable Checkboxes in Excel: Step by Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Dropdown Based on Another Cell: GuideYes. In Google Sheets, you can make one dropdown depend on another by creating named ranges for each…
- Excel Slicers Explained: Filtering PivotTables and TablesExcel slicers are on-sheet buttons that let you filter an Excel Table or PivotTable by clicking values such…
- Excel SUM Visible Rows: Add Filtered Data CorrectlyWith values in B2:B100, use =SUBTOTAL(109,B2:B100) to add the visible rows and exclude rows removed by a filter…
- How to Remove a Blocked Sender in Outlook SettingsTo remove a blocked sender in Outlook, open your email account’s junk or blocked-senders settings, find the address…
- Excel 3D Scatter Plot: Options and WorkaroundsExcel for Microsoft 365 does not include a native general-purpose 3D scatter chart with X, Y, and Z…
- Searchable Dropdown in Excel UserForms: How It WorksYes. In an Excel VBA UserForm, a ComboBox can provide a searchable dropdown: its built-in autocomplete can complete…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.