While trying to enter a formula in your Excel sheet, it doesn’t function for some reason. Rather, it informs you of a circular reference. Millions of users are stuck with the same issue every day as a result of asking an Excel function to calculate its own cell. This quick lesson walks you through the basics of circular reference error in Excel and why you should avoid them. You’ll also learn how to analyze, find, and fix circular reference error in Excel spreadsheets, as well as how to enable and use circular formulas if none of the prior options are available.
What is A Circular Reference?
When you end up with a formula in a cell, you create a circular reference – which performs the calculation using the cell reference (in which it was entered) like a snake chasing its own tail.
In other words, a circular reference happens when a formula, either directly or indirectly, refers back to its own cell, resulting in an unending loop of computations. The cell’s value will be changed every time the unending reference loop is not stopped.
Here’s an example to help you understand.
In the following table, we’ve shown statistics of the world’s one of biggest brands Apple’s revenue over the past decade. For instance, Using the SUM Function in the C16 field, we’re attempting to compute their total revenue. However, we gave the range C5:C16 to the SUM Function.
It creates an endless loop because the supplied range also contains the C16 cell (in which the result needs to be written). Consequently, Excel will simply keep adding the new value to cell C16, which will keep increasing exponentially.
This triggers Excel to display a circular reference warning, as shown in the image below.
📝 Note.
- This is not an error because it will not halt or prompt the user to alter the computations. It’s a notification indicating the user’s computations are likely to be incorrect.
- In many cases, when you enter more than one formula with a circular reference, Excel doesn’t display the warning message repeatedly.
Apply Iterative Calculations to Enable/Disable Circular References Error
Iterative computations are an interesting feature in Excel. If the user disables iterative calculations, Excel shows a Circular Reference alert and shows a 0 instead of the actual result in the cell. Because it is a never-ending loop, this is the case.
If you’re working with a huge dataset and want Excel to sit back and let you get on with your work without bothering you with circular reference warnings, you may utilize Iterative Calculations.
Enable/Disable Iterative Calculations
In the below screenshot, you can see there is a circular reference in cell C16 indicated in the status bar of the worksheet. Consequently, it results in zero (0) in cell C16. Now, if you want your job to be done without any troubling issues, you need to enable Iterative calculations.
Let’s look at how to enable or disable iterative calculations in Excel now that we’ve established how they function. To enable or disable iterative calculations in Excel, follow the steps below.
Steps:
- Go to the File tab ⇨ Options ⇨ Formulas.
- Mark the check box Enable iterative calculation.
- Press Enter.
Consequently, you can see that the circular reference dialog is illuminated with calculate as shown in the screenshot below. Now, there is a value in cell 16 instead of zero (0).
For that, each cell previously related with circular reference will be calculated.
Maximum Iterations & Maximum Change Parameters
The Iterative Calculations have two parameters:
Maximum Iterations
This loop will be repeated when Excel calculates the final result. This is completely under your control. Keep in mind that more iterations equal more Excel operating, which implies the machine will need more materials and processing capacity. It would also take longer.
Maximum Change
The maximum change is the number that must be achieved in order for the iteration to continue. It’s all about the outcome’s precision. Make this value as low as possible so that accurate results may be achieved. By default, the maximum change is set at 0.001.
Why Should We Avoid Enabling Iterative Calculations Deliberately?
It’s not a good idea to use Circular References in Excel. When you enable iterative calculations, the deliberate usage of circular references is a tricky approach to making things work that chews up a lot of resources and computing power. When you have to fix a few of them, it can often yield unwanted or inaccurate consequences, which can be frustrating.
Find Circular References Error in Excel
When a circular reference error occurs, you will receive a warning, but you will still need to find out which cell the issue occurred in. It will be easier to handle and solve the mistake once you know the particular cell position of the error.
These techniques are very useful when dealing with big datasets.
1. Use Error Checking Drop-down to Find Circular Reference Error in Excel
Here’s how to use the Ribbon in Excel to find circular references.
Step 1:
- Go to the Formulas tab
- Click on the Error Checking drop-down menu.
Step 2:
- Select Circular References from the option.
Excel will display all of the worksheet’s circular references in this section.
As you can see in the screenshot above, the spreadsheet we’re utilizing has one circular reference in cell C16.
Read More: How to Find a Circular Reference in Excel
2. Use Status Bar to Find Circular Reference Error in Excel
It’s fairly simple to find circular references using the status bar. If the worksheet has a circular reference, the user will be able to view it in the status bar below the worksheet names.
📝 Note.
- This feature is disabled when the Iterative Calculation option is turned on, so you need to turn it off before you start checking the workbook for circular references.
- It only displays the most recent circular reference and allows the user to go backward from there.
Read More: How to Allow Circular Reference in Excel
Fix Circular Reference Error in Excel
Circular references cannot be fixed in Excel with a single click. To deal with circular references, you must remove them one by one.
For example, after finding the error in cell C16, you need to rewrite the correct formula or select the correct range.
Step 1:
- Select the correct range to fix the error in cell C16.
Step 2:
- Press Enter in cell C16 to see the results.
Read More: How to Remove Circular Reference in Excel
Trace Relationships Between Formulas and Cells
When an Excel circular reference isn’t apparent, you may use the Trace Precedents and Trace Dependents features to draw one or more lines to demonstrate which cells impact or are affected by the selected cell.
In the below screenshot, we have shown the prospected revenue of Apple’s in 2022. Now we want to Sum up the total revenue from the years 2011 to 2022. To do this, we type the following formula in cell D10.
But as we have a circular reference error in cell C16, there will error value in cell D10. In this issue, we use Trace Precedents and Trace Dependents to trace relationships between formulas and cells.
Trace Precedents to Relate Circular Reference Error in Excel
Traces the cells that supply data to a formula, drawing lines to show which cells have an impact on the selected cell. To do this follow the steps below.
Steps:
- Go to the Formulas tab in Excel.
- Select Trace Precedents.
Therefore, you will see arrows that indicate the cells affect the active ones.
📝 Note. shortcut to Trace Precedents: Alt+T U T
Trace Dependents to Relate Circular Reference Error in Excel
Draw lines indicating which cells are influenced by the selected cell and trace cells that are dependent on the active cell. To do this follow the steps below.
Steps:
- Go to the Formulas tab in Excel.
- Select Trace Decedents.
As a result, It displays which cells have formulas that refer to the currently active cell.
📝 Note. shortcut to Trace Decedents: Alt+T U D
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
To conclude, I hope this article has provided detailed guidance to fix circular reference error in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.
Stay with us & keep learning.
