Prantick Bala
In Excel, occurring a Circular Reference means a formula cell directly or indirectly refers to itself. Excel cannot calculate automatically when a worksheet has a circular reference. If you leave the circular reference in, Excel calculates each cell included in the circular reference using the previous iteration’s values. This creates an infinite loop that drastically slows down your spreadsheet.
Now, we will discuss more about what is Circular Reference in Excel and how you can deal with it.
Download Practice Workbook
Types of Circular Reference
If the cell in which you are inserting the formula is mentioned in the reference in any way, it will be called a circular reference. There are mainly two types of circular reference which are direct and indirect. If you insert the cell in which you are typing the formula in the reference of the formula, it is a direct circular reference. If you insert a cell in the reference of another cell, where the first cell has the second cell as reference, it is an indirect circular reference.
1. Direct Circular Reference
Consider the following dataset where we will find the total sales by adding up the cells D5 to D11 in cell D12.
Now, if we accidentally add up cell D12 with those cells in cell D12 it will cause a direct circular reference.
If we press ENTER a dialog box with a circular reference error message will appear.
If we click on OK, we can see the formula is showing 0 rather than the summation. This is happening because of the direct circular reference.
2. Indirect Circular Reference
Look at the following example where the formula in cell D6 has reference to cell C4 and the formula in cell B8 has reference to cell D6. Now, if the formula in cell C4 has reference to cell B8, it will cause a circular referencing. Here the formula in cell C4 doesn’t refer to itself directly but if we trace the precedents we can see at some point C4 is used as a reference.
If we press ENTER, cell C4 will show 0 and how the cells are related will be shown by blue arrows.
Finding and Correcting Circular Reference in Excel
To get your calculation correctly, finding out the circular references and fixing them are important. There are several ways to find out the circular reference from your worksheet and now we will discuss them.
1. Show Formula Option to find Circular Reference
You can manually find out the circular references of your datasheet by displaying the formula.
➤ Go to Formulas > Formula Auditing > Show Formulas.
It will display all the formulas of your worksheet. Now you can easily identify the circular references by evaluating the formula manually.
2. By Error Checking Options
You can also identify the circular reference of your worksheet by Error Checking options.
➤ Go to Formulas > Formula Auditing > Error Checking > Circular References.
Here you will see all the cells which have circular references.
Now you can take the necessary steps to fix the calculation such as correcting the reference in the formula.
Read More: How to Fix Circular Reference Error in Excel (A Detailed Guideline)
3. From Status Bar
The easiest way to find out the circular reference is to look into the status bar. If your worksheet has any circular reference, it will be shown in the status bar.
Once you have found the cell which contains circular reference you can now easily take necessary action to deal with the circular reference.
Trace Relationship in Circular Reference
There are two tracing methods that will allow you to trace relationships between formulas and cells of circular references. The tracing methods are trace precedents and trace dependents. These tracing tools can assist the user in resolving circular references by displaying a path connecting the references via a line created between cells responding to the circular references.
1. Trace Precedents
The trace precedents feature keeps track of the cells that the current cell depends on. These are the cells that the current formula relies on for the data it requires. This option will generate lines to show which cells are influencing the active cell.
To trace the precedents. First,
➤ Select the cell with circular reference.
➤ Go to Formulas > Formula Auditing > Trace Precedents or press ALT+T+U+T
As a result, you will see, a blue arrow will appear from the precedent of the active cell to the active cell.
2. Trace Dependents
The trace dependents function keeps track of the cells that are dependent on the active cell, meaning they rely on the active cell for the data they require to create outcomes. This feature will draw lines to the cells that are dependent on the active cell.
To trace the dependents. First,
➤ Select the cell with circular reference.
➤ Go to Formulas > Formula Auditing > Trace Dependents or press ALT+T+U+D
As a result, you will see, a blue arrow will appear from the active cell to the dependent cell.
Enabling Iterative Calculation to Allow Circular References
The iterative calculation is an interesting feature in Excel. When the iterative calculation is disabled, Excel returns a Circular Reference prompt and returns a 0 in the cell instead of the actual result. Now, if you want to allow circular references in your worksheet, you can enable the iterative calculation and it will allow you to perform your calculations. You can also set the number of iterations allowed. Hence, you can stop the circular reference infinite loops.
To turn on the iterative calculation,
➤ Go to the File tab and select Options.
It will open the Excel Options window. Now,
➤ Go to the Formulas tab, check the Enable iterative calculation box, and click on OK.
It will enable the iterative calculation and now you can use circular reference.
If you go back to your worksheet, this time you will see the formula cell is showing a value instead of 0 and there is no circular reference error. That means now Excel is allowing circular references.
Read More: How to Allow Circular Reference in Excel and Its Usage
Why Should Iterative Calculation Remain Disable?
Enabling Iterative calculation in Excel is not recommended unless it is required. When Iterative calculation is active, Excel consumes a lot of power to operate and takes much more time to make a simple calculation. Besides that, miscalculations from circular references can occur and remain unnoticed, if Iterative calculation is turned on. So we should keep iterative calculation inactive unless it is needed.
Conclusion
I hope now you know what a circular reference is in Excel and How you can deal with it. If you have any confusion, please leave a comment.
