Excel circular reference error occurs when Excel creates an endless loop in the calculation. This issue arises when the formula given for calculation refers to the same result as the cell formula.
When this error occurs, you will find the below warning message in your Excel.
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, changing these references or moving formulas to different cells.
This message doesn’t stop functioning calculation. It just warns the user that they are performing incorrect calculations.
Let’s understand this with an example.
In figure below, you can see few numbers under A section. Suppose you want the sum of those numbers that is from A1 cell to A6 cell using SUM function. For which you have to pass the range in A7 cell as A1:A6.
Now instead of passing A1:A6, you have passed A1:A7 in cell A7 by mistakenly. Here, Excel creates a loop as it continuously adds the new number in A7 cell.
Now let’s see the methods to remove circular reference error in Excel.
How To Eliminate Circular Reference Error In Excel?
Some of the easy methods are mentioned below to fix circular reference error in Excel. Go through one by one and fix this issue.
#1. Enable Iterative Calculation:
Iterative calculation is the process of recalculating repeatedly till the specific condition is met. In Excel, by default Iterative Calculations are turned off. Circular References works when these are enabled in Excel.
So, follow below steps to enable Iterative Calculations in Excel:
(Only for Excel 2010, Excel 2013, Excel 2016and Excel 2019 users)
- Click on File menu in Excel.
- Click on Options from menu.
- Select Formulas option,
- Now under Calculation options, click on Enable iterative calculation checkbox.
(Only for Excel 2007 users)
- Click Office button in Excel.
- Select Excel options.
- Select Formulas option.
- Then click on Iteration area.
(Only for Excel 2003 and earlier)
- Click on Menu in Excel.
- Click on Tools.
- Select Options.
- Select Calculation tab.
- Choose Iterative Calculation.
These are the above given steps for different Excel users so that you can enable your Iterative Calculation in Excel.
After enabling, go to the options given below Iterative Calculation. You have to specify those two options:
- Maximum Iterations: By default this box is set to 100 which is the number of times for recalculating formula. Calculation time depends on the number of Maximum Iterations.
- Maximum Change: By default this box is set to 0.001 which is the number of times for calculation result to get changed.
You can get accurate result of your calculation only if you select the smaller number for Maximum Change and select the higher number for Maximum Iterations.
You May Also Like: How To Reduce Excel File Size?
#2. Tracing method:
To fix circular reference warning in Excel, you can also remove circular reference one by one or trace back to source and delete the first formula.
There are two methods for tracing, just go through the below steps:
- Click on Formulas in Excel.
- In Formula Auditing section, you will find both the tracing feature that is Trace Precedents and Trace Dependents.
In your Excel, when the current formula depends on another cell for data then using this feature these cells are traced back.
On clicking Trace Precedents, it will show you all the cells that are affecting the current cell by drawing a line.
Example: In the below figure, you can see a line indicating B5, B3 and B2 cell leading B5.
In your Excel, when the cell depends on current cells to produce final result then using this feature these cells are traced back.
On clicking Trace Dependents, it will show you the cells which are dependent on current cell by drawing a line.
Example: In the below figure, you can see a line indicating B5 which is affecting the cell as B4 is depending on B5.
#3. Find Circular References In Excel:
Mostly the cell causing circular reference error gets selected automatically with a warning message. In case, if you get only the circular reference warning message then you can go for this method to locate the cells:
- Click on Formulas tab in Excel.
- In Formula Auditing section, click on Error Checking.
- Select Circular References.
- Now Excel will show you the cells causing circular reference error.
Try the above solution which may help you to fix Excel circular reference error.
To prevent this error, avoid using circular reference in Excel as while showing the warning message it also leads to various other issues in Excel.
I hope this post was helpful to you.