Sometimes, when using the formula in Excel, you may receive a warning message “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.”
If you click OK in the error pop-up or close the dialogue box, you’ll get any one of these results in the formula cell.
- 0: A zero value as output.
- Last Calculated Value: In case the formula was able to calculate itself, it returns the last calculated result.
Apart from the incorrect output, Circular Reference can also cause lag in Excel Workbook Calculations because of its endless loop.
So, today, let’s learn what Circular Reference is in Excel and the ways to fix it.
What is Circular Reference in Excel?
When you enter an Excel formula that directly or indirectly refers to its own cell, it’s called a Circular Reference.
If I have to be more straightforward, you are referencing the same output cell in the formula too.
For example, entering the =A1 + B1 formula in the B1 cell creates a circular reference. It means here the B1 cell is trying to calculate itself in the formula which causes an infinite loop and thus results in an error.
And of course, the Circular Reference occurs mostly when you mistakenly enter the incorrect cell reference in the formula just like the other cell errors.
You may also receive an error message in one of these given instances.
- When you create or open an Excel file with Circular Reference.
- When you delete all existing Circular references, but again set up another circular reference.
- When you enter a formula that causes Circular Reference.
However, note that in some situations, Excel might not send you an error message even if you have a circular reference in the Workbook.
How to Fix Circular References in Excel?
Since we’ve identified the causes for Circular References in Excel, you can troubleshoot the problem with these proven fixes.
Check Circular References and Correct Formula
For Circular References, it can be difficult to pinpoint which cell is exactly causing the error at first. This is because you’ll only get an error pop-up and not the message in cells like the #SPILL!, #REF!.
Luckily, there is a menu to find the cell containing Circular Reference in the Excel Ribbon.
- Head to the Formulas Tab and expand Error Checking.
- Select Circular References and click on the Cell. You can also see the Circular Reference in the Status Bar.
- Then, to solve the error, either edit the formula or delete it.
But, what happens if you have multiple circular references in the same sheet? Well, the Error Checking menu will detect and display the last cell containing the formula.
For Instance, if both A5 and B4 are error cells, it will show B4 since it was calculated at last.
Use Trace Dependents
If your Circular Reference menu is greyed out, it means that there are no such formula errors in the current spreadsheet.
However, if you still get the message while the option is grey, use the Trace Dependents menu to track the cell.
Click on the Cell value you used in your formula. From the Formulas tab, click on the Trace Dependents.
The Blue arrow will show the Formula cell that may contain the Circular Reference.
Click on the Cell and revise the formula to correct the error.
Check Name Manager
Sometimes, the external file references in your workbook may be causing the Circular Reference.
Since you cannot identify the cell with Trace Dependents or Error Checking tool, we will opt for the Name Manager.
- In the Formulas Tab, click on Name Manager.
- On the Name Manager window, click a Name and see the Refers to section. It displays the Workbook and Cell of Named Range.
Once you know the source that your workbook is linked to, you can head to that location and modify the formula. Or, delete the external links from your workbook.
Enable Iterative Calculations
It’s an exceptional case when some users actually enter the Circular Reference in the Formula intentionally to do iterative calculations.
For Instance, you might use it to enter AutoDate with IF and TODAY functions.
So, this fix is for users who want Excel to calculate circular references. Here, we will enable the Iterative Calculations from the Options menu.
By default, Excel disables the Iterative Calculation as it can lead to slow performance of your PC. That is why I would have to give a heads-up before you change the settings as it can also result in incorrect output.
- On your workbook, click on the File tab.
- Choose Options.
- Click the Formulas category. Below the Calculation options, check the box for Enable iterative calculation.
- Under Maximum change, set the Number. Click OK.
