How to Remove Circular References in Excel (2024 Update)
In this article, we will show you how to remove circular references in Excel. Read on to learn more!
How to Remove a Circular Reference in Excel
To remove circular references in Excel, follow these steps:
1. Identify the Circular Reference
Excel will typically alert you with a warning when you enter a formula that creates a circular reference. The warning message includes the cell that contains the circular reference.
For example, A1 contains the value 100. A2 is intended to contain a total, but by mistake, you type in =A1+A2. In this case, A2 is trying to add its own value, which is undefined, to A1, creating a circular reference.
2. Correct the Formula
Once you’ve identified the cell, examine the formula in the formula bar to understand why it’s referring to itself.
Adjust the formula so that it no longer refers to its own cell. You may need to introduce new cells to hold intermediate results or restructure your calculation logic.
Using our example above, we can simply move the formula to A3:
Remove a Circular Reference in Excel Using Iterative Calculations
If your task requires using a cell’s own value in its calculation (e.g., for running totals or iterative calculations), you can enable iterative calculations to remove circular references. Here’s how:
1. Open Excel Options
Click on the “File” tab in the top-left corner of Excel.
From the sidebar, select “Options” found at the bottom.
2. Access Formula Settings
In the “Excel Options” dialog, click on the “Formulas” category on the left panel.
3. Enable Iterative Calculations
In the “Calculation options” section, you will find the “Enable iterative calculation” checkbox. Check this box to allow Excel to perform iterative calculations.
4. Set Iteration Options
Upon enabling iterative calculations, you need to specify two important settings:
Maximum Iterations: This determines the maximum number of times Excel will recalculate the formula. A higher number gives Excel more opportunities to reach a closer approximation but may slow down performance.
Maximum Change: This sets the threshold for changes between calculation results. Excel stops calculating when the change in value is less than or equal to this setting, indicating that the result has stabilized.
Set these values based on your specific needs. For typical calculations, starting with 100 iterations and a maximum change of 0.001 works well.
5. Apply and Close
After adjusting the settings, click “OK” at the bottom of the “Excel Options” dialog to apply the changes and close the dialog.
We hope that you now have a better understanding of how to remove circular references in Excel. If you enjoyed this article, you might also like our articles on how to reference a different sheet in Excel and how to cross reference two lists in Excel.
