In Excel, users often encounter a common glitch known as “Circular References.” It’s frustrating to face this error as it disturbs your workflow and ruins your mood. Well, in this article, I delve into the details of circular reference error, exploring the common causes, and other vital information along with how to fix circular reference in Excel.
There are two circular reference errors that you might get:
“There are one or more circular references where a formula refers to its own cell either directly or indirectly.”
“Careful, we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly.”
So, let’s dig into this post and learn to solve the above warnings for maintaining accurate calculations in your Excel spreadsheets.
To fix corrupted Excel files, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Try Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Quick Navigation:
- Part 1- What Is a Circular Reference Error in Excel?
- Part 2- When Does Circular References Error Occur?
- Part 3- How to Fix Circular Reference in Excel?
- Part 4- What Causes a Circular Reference in Excel?
- Part 5- Why You Should Avoid Using Circular References In Excel?
- Part 6- How to Prevent Excel Circular References Error?
What Is a Circular Reference Error in Excel?
Circular reference is a hiccup that occurs when you enter any formula in the Excel spreadsheet but it doesn’t work. Instead of that, you will start receiving circular reference error.
Well, such an issue arises because your assigned Excel formula is trying to calculate itself.
Let’s take an example for better understanding:
In cell B3 you are using the formula =D1+D2+D3. So, in this case, you are using a function that includes the reference to itself.
To tackle the situation, you need to move your formula to some other cell. For this, just press the Ctrl+X keys from your keyboard to cut the formula, and after selecting another cell press the Ctrl+V keys to paste it.
When Does Circular References Error Occur?
This error usually appears when:
- Trying to create a first circular reference in the Excel workbook.
- Attempting to open the XLS/XLXS workbook with the unsolved circular reference.
- Creating the new circular reference later removing previous illustrations.
How to Fix Circular Reference in Excel?
Method 1- Find Circular Reference In Excel
You should first find the circular reference and then try to fix this problem by following the below steps:
- Hit the Formulas.
- Tap the Error Checking button and hit the arrow sign present next to it.
- From its drop-down choose Circular References and again hit the side arrow sign.
- From the submenu hit the first cell.
- Now make a review of the formula cell.
- If you are in doubt that the cell is responsible for circular reference error. Then hit the next cell present in the Circular References submenu, if it is available.
- Continue to the review option and make corrections in the circular reference.
- Very soon your status bar will notify you that “Circular Reference” is present in your workbook along with the address of that cell.
- If these circular references are present in some other sheets then the status bar will only show you the “Circular References” word without any cell address.
Here are some more tricks that you can try to remove circular reference in Excel:
- After assigning the formula in a cell, make a check whether you have referenced that formula cell in the cell itself.
Suppose, in cell B3 you have added a formula = (A1+A2)/A3. Then this will cause such circular reference errors.
- The second option is to check whether there are some indirect references.
This will happen when you enter your formula in cell A1 and this uses another formula present in cell B1 which in turn refers back to the A1 cell.
If this whole process confuses you then just think over it how much your Excel application gets confused.
Method 2- Enable / Disable Circular References to Fix Circular Reference Errors in Excel
As it is already mentioned, that iterative calculation by default gets turned off in Excel. In order to make your circular formulas work you need to enable the iterative calculations within your Excel file.
For Excel 2003 users:
- Click on Iterative Calculation option present within the Menu > Tools > Options > Calculation tab.
For Excel 2007 users:
- Click the Office button > Excel options > Formulas > Iteration area.
For Excel 2019/ 2016/ 2013/ 2010 users:
- Hit the File>Options. Now tap on the formulas tab and choose to Enable iterative calculation check box present within the “Calculation options”
After turning on the iterative calculations you need to specify these two options:
- Maximum Iterations box –
This option is for specifying the number of times the formula will be recalculated. If you set the higher number of iterations then the calculation will take more time.
- Maximum Change box –
Using this option you can set the maximum change between the calculation results. It’s better to set this number small as this will give a more exact result. If you set a higher number then Excel will take a longer time to do calculations.
Note: Usually Excel stops doing calculations after 100 iterations. Or when all values present within the circular reference are been modified by less than 0.001 between iterations.
What Causes a Circular Reference in Excel?
Excel circular reference error occurs when the formula either directly or indirectly refers to the cell in which it is already contained. Due to this, the formula recalculates its already obtained result which generates such type of error.
If your Excel workbook has any circular reference then automatic calculation won’t be performed. Instead, you need to make use of the Excel error-checking feature to locate the location of the circular references and after that remove them.
If in case you leave them as it is then Excel will calculate each cell involved in this circular reference by making use of the results obtained from the previous iteration.
Note: Here iteration means the process of repeated recalculation until it matches specific numeric criteria.
Also Read: #VALUE! Error in Excel Demystified: A Step-by-Step Guide
Why You Should Avoid Using Circular References In Excel?
The idea of using the circular reference in Excel is not a recommended approach. I am telling this because apart from displaying a warning message or performance issue it leads to several other issues that won’t appear immediately.
Suppose that you have selected a cell that contains the circular reference and then mistakenly, you have turned off the formula editing mode. So when you press the enter button without doing any modification in the formula it will give you the result zero.
That’s the reason why many Excel experts also suggest avoiding the usage of circular references in your Excel workbook if it’s possible.
Also Read: Excel This Value Doesn’t Match The Data Validation Error
How to Prevent Excel Circular References Error?
Follow the below precautionary measures to avoid this error:
- When you need to switch between the cells in the circular reference worksheet then do this by double-clicking the tracer arrow. This arrow indicates those cell that affects the value of your currently selected cell.
- Show the tracer arrow by tapping on the Formulas after that either choose Trace Dependents or Trace Precedents.
- Close all your opened workbooks and then create a new workbook. After that enter the formula which contains a circular reference.
Related FAQs:
Can Circular References Cause Data Corruption in Excel?
Yes, circular references cause data corruption in Excel.
Is Circular Reference Good or Bad?
Frankly speaking, a circular reference is still a bad thing.
How Do I Allow Circular References in Excel?
You can allow circular references in Excel by following these steps:
- Go to File tab >> choose ‘Options’.
- Choose a ‘Formulas’ tab >> ‘Calculation options’ section.
- Check the ‘Enable iterative calculation’ box.
- Now, change the ‘Maximum Iterations option field to 100' >> hit ‘OK’.
What Is the Alternative to Circular Reference in Excel?
There are various alternative methods to circular reference in MS Excel including copy and paste macro method, algebraic method, etc.
Is It Always Necessary to Resolve Circular References?
Circular references may not always affect your calculations, but it is advisable to resolve them to preserve data accuracy.
What Should I Do If I Encounter Circular References Frequently?
If you frequently encounter circular reference warnings, seek assistance from professionals or Excel communities.
Final Verdict
Well, managing circular references is a vital aspect of maintaining accurate & reliable Excel spreadsheets. But while using this feature you may encounter warnings anytime. To deal with such situations, you must follow the solutions on how to remove circular reference in Excel mentioned above and boost your Excel experience.
That’s all…