How to get rid of circular references?
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?
Circular references occur in Excel when a formula refers to itself either directly or indirectly. This can lead to incorrect or unpredictable results, and it’s important to resolve them.
Go to the Formulas tab.
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”.
Leave a Reply