A circular reference occurs when you end up having a formula in a cell – which in itself uses the cell reference (in which it’s been entered) for the calculation. If this statement seems a bit confusing, don’t worry by the end of this tutorial it will start to make sense. Show
In simple terms, a circular reference happens when a formula references back to its own cell directly or indirectly, creating an endless loop of calculations. This endless reference loop, if not stopped will keep changing the cell's value every time. For quick understanding, here’s an example. In our example, there are a few numbers in column A and we are trying to calculate their sum using the SUM function in A7 cell. But inside the SUM function, we have passed the range as A1:A7. Since the supplied range also includes A7 cell (in which the result needs to be populated) so it creates an endless loop as Excel just keeps on adding the new value in cell A7, which keeps on incrementing. This triggers Excel's circular reference warning that says, "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells.". Keep in mind that this is not an error since it will not stop further calculations or ask the user to change them. It is a warning that warns the user that they could probably have incorrect calculations.
Direct and Indirect Circular ReferencesCircular references can be categorized into two types – 1) Direct Circular References 2) Indirect Circular References. Let's try to understand both of these first. Direct Circular ReferenceA direct circular reference is pretty straightforward. The direct circular reference warning message shows up when the formula in a cell is referring to its own cell directly. For example, see the data below.
This is an example of a direct circular reference in Excel. Indirect Circular ReferenceAs the name suggests, an indirect circular reference takes place when a value in a formula refers to its cell, not directly but at some level. See the simple example below for a better understanding. For creating an indirect circular reference example, we will have to write a chain of formulas using values starting from the first cell.
Now, let's try to see and understand how to find circular reference issues in excel. How to Find Circular References in ExcelWhile you will get the circular reference warning when it occurs, however, you will still need to figure out in which cell the error has occurred. After knowing the exact cell location of the error it will be easier to handle and fix it. These methods are particularly helpful when you are working with large datasets. 1) Error Checking drop-down in the RibbonHere’s how you can find circular references in Excel using the Ribbon.
As you can see in the screenshot above, there are 4 circular references in the worksheet that we are using. 2) Using Status barFinding circular references using the status bar is very easy. If a circular reference exists in the worksheet, the user will be able to see it in the status bar below the worksheet names. Note: It only shows the latest circular reference and the user can backtrack from there. As seen in the example below, there are two circular references notably in B8 and C8 but the status bar only shows C8. Iterative CalculationsIterative calculations are an interesting feature in Excel. What iterative calculations mean is that if the user keeps them disabled as they usually are, Excel returns a Circular Reference prompt and returns a 0 in the cell instead of the actual result. This is because it is an endless loop. Now, the thing is that if you want to tell Excel to sit out and let you do your job without bothering you about those pesky circular reference warnings, you can enable Iterative Calculations and it will allow you to perform your calculations. Not only that, but it will also allow you to set a fixed number of maximum iterations. If you wish to enable iterative calculations, you can set the number of iterations allowed. Hence, you can stop the circular reference infinite loops and can have a tiny bit of control over such calculations. How to Enable/Disable Iterative Calculations in ExcelNow that we have established how iterative calculations work, let’s show you how you can enable or disable iterative calculations in Excel. Follow the below steps to enable or disable iterative calculations in excel.
Maximum Iterations & Maximum Change ParametersThe two parameters of the Iterative Calculations are: Maximum iterations: This is the loop that Excel will run when it is calculating the final result. You can set this as you want. Remember that more iterations mean more processing for Excel which will in turn use more resources and processing power from the computer. It would also take more time. Maximum change: The maximum change is the value that needs to be achieved in order for the iteration to move further. It is about the accuracy of the result. Make this value small as it will produce accurate results. The default Maximum Change is 0.001. Deliberately Using Circular ReferencesThe deliberate use of Circular references is not a very good idea, though sometimes it can help you to get away with poorly developed logic or formulas. But doing this is not recommended at all. However, for educational purposes let's try and understand how to get things working with deliberate circular references. The first step is to enable Iterative Calculation in Excel. Once you have turned on Iterative Calculation and set your maximum iterations, you can start using circular references to your benefit. Let us demonstrate that by using an example. Let’s say that the user requires to manipulate the data in a way that two cells depend on each other for values causing a circular reference issue. Since the iterative calculation is enabled, there won’t be a prompt. In the example below, the cells B4 and B5 depend on each other, creating a circular reference. Since the iterative calculation is enabled, there will not be an error or a 0 in the results. Instead, Excel will try calculating the results. In the example, the Agent fee is 20% of the total earnings of the player. Since the total earning and agent fee are dependent on each other, it can still be calculated if iterative calculation is enabled. To use it as we see fit, we must first apply our desired formula in the net earnings cell. Now the Agent fee must be calculated which we are going to set at 20% of total earnings but the total earnings will then have to subtract the Agent fee from itself, hence the circular reference. As you can see, it works perfectly. Why Should We Avoid Using Circular References Deliberately?Using Circular References in Excel is not recommended at all. Deliberate use of circular references is a slippery method to make things work that eats up a lot of resources and processing power when being used with iterative calculations. It can often produce undesired or inaccurate results that can cause frustration when you have to fix a few of them. How to fix Circular References in ExcelFixing circular references in Excel is not possible with a single click. To handle circular references, you have to eliminate them one by one. You can trace it back to the source and remove the starting formula to fix it, or you can remove it one by one. There are two tracing methods that will allow you to remove circular references by tracing relationships between formulas and cells. To access the tracing methods, you have to follow the following steps:
These tracing features can help the user in fixing circular references by providing a path connecting the references through a line that is drawn between cells that are responding to the circular references. The two tracing options work as follows: Trace PrecedentsThe trace precedents feature tracks back cells that the current cell depends on. These are the cells on which the current formula is dependent for the data that it needs. This option will draw lines that will tell which cells are affecting the active cell. In the example below, the cells affecting B5 are B2, B3, and B4. Hence, when we click on Trace Precedents, it draws a line indicating B2, B3, and B5 cells leading to B5. Shortcut for Trace Precedents: ALT + T U T Trace DependentsThe trace dependents feature tracks the cells that are dependent on the active cell, the cells that depend on the current cell for the data they need to produce results. This feature will draw lines to the cells that are dependent on the active cell. In the example below, the cell being affected by B5 is B4. It is dependent on B5 for its value. Hence, when we use trace dependents, it draws a line from B5 to B4, indicating that B4 is dependent on B5. Shortcut for Trace Dependents: ALT + T U D So, this was all about circular references in Excel. Hopefully, by now, you have a good idea of how circular references work, how you can find/fix them, and how you can use them if you desire. How do you fix Microsoft Excel Cannot calculate a formula there is a circular reference in an open workbook?To fix the problem, you can move the formula to another cell. Press Ctrl+X to cut the formula, select another cell, and press Ctrl+V to paste it. You can also try one of these techniques: If you just entered a formula, start with that cell and check to see if you refer to the cell itself.
How do you find a circular reference in Excel that Cannot be listed?Firstly, go to the Formulas tab.. Secondly, from the excel ribbon under the Formulas tab select the drop-down “Error Checking”. ... . The above action is showing in a sidebar that circular reference is happening at cell C11 in our worksheet.. In short: Go to Formulas > Error Checking > Circular References.. How do you fix a circular reference error in a workbook?Go to the Formulas tab and click on the Error Checking drop-down menu.. Select Circular References from the drop-down menu.. Here Excel will show you all the circular references that are in the worksheet.. Click on whichever circular reference you want and it will take you to that particular cell to solve the issue.. Why does Excel keep saying I have circular references?If you see a circular reference warning in Excel, it usually points to a formula in a cell that, in some way, refers back to itself. As the formula can break its own result, this creates a circular reference.
|