In this Excel tutorial, you will learn how to remove the circular references in an Excel file.
What is a circular reference?
Let us understand what circular reference is. A circular reference happens when a formula directly or indirectly refers to its own cell. This causes the formula to use the result in the calculation and it runs in a loop, causing an error. When there are circular reference errors, Excel cannot do the other formula calculations automatically.
If you leave them in, Excel calculates each cell involved in the circular reference by using the results of the previous iteration. An iteration is a repeated recalculation until a specific numerical condition is met. By default, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, unless you change the Excel option.
A circular reference can cause an infinite loop in the spreadsheet.
Let's create a circular reference now to look at it:
I wrote a value in cell A3, which is 10.
In cell B3 I wrote a formula: = A3+B3.
We are already putting a formula in B3 and using the same in the formula.
On clicking enter/tab, it gives the following error:
On clicking ok it gives the wrong results = 0 and at the bottom it keeps showing a warning:
How to find the circular reference?
1. Go to the Formulas tab.
2.Click on error checking:
In error checking, click on circular references:
It shows the cells that have the issue, and we can correct them.
If you cannot figure out if the cell is the cause of the circular reference, click the next cell in the Circular References submenu, if available.
Continue to review and correct the circular reference until the status bar no longer displays the word "Circular".
Now we will learn how to remove the circular references.
How to enable / disable circular references in Excel
As noted earlier, iterative calculations are usually turned off in Excel by default (in this context, iteration is the repeated recalculation until a specific numeric condition is met). For circular formulas to work, you must enable iterative calculations in your Excel workbook.
In Excel 2007, click the Office button > Excel options > Formulas > Iteration area.
In Excel 2003 and earlier, the Iterative Calculation option resides under Menu > Tools > Options > Calculation tab. When you turn on iterative calculations, you must specify the following two options:
- Maximum Iterations box - specifies how many times the formula should be recalculated. The higher the number of iterations, the more time the calculation takes.
- Maximum Change box - specifies the maximum change between calculation results. The smaller the number, the more accurate the result you get and the more time Excel takes to calculate the worksheet. The default settings are 100 for Maximum Iterations, and 0.001 for Maximum Change. What it means is that Microsoft Excel will stop calculating your circular formula after 100 iterations or after a change of less than 0.001 between iterations, whichever comes first.