80% of the people don’t know what a circular reference in Excel is. They just run some complex formulas in any active cell and end up with an error dialog dox. The next thing they do is GOOGLE!
Are you among those 80% users?
Then surely you saw the error – “Careful, there are one or more circular references in your workbook”
Worry no more. Keep Reading this post as it contains an in-depth explanation of the problem.
Importance of Understanding Circular References in Excel
Nobody wants an incorrect result in their workbook. Excel is made to make our tasks easy, not give us a headache.
You don’t want your boss to know that your accuracies wore off, do you?
Here are some points to show you the extreme importance of Excel’s circular references:
Avoid Errors and Improve Performance
Circular references can cause errors in your calculations and slow down the performance of your workbook, especially in large and complex models.
By understanding how circular references work, you can find and resolve incorrect results, ensuring the accuracy of your calculations.
Ensuring Data Integrity
Circular references can compromise the integrity of your data, as they can create incorrect values in cells that depend on each other.
Knowing how circular references work can help you troubleshoot formula errors and other issues in your Excel workbooks.
This can save you time and help you resolve problems more quickly.
Enabling Iterative Calculations
Circular References aren’t bad all the time!
In some cases, iterative calculations that use circular references may be necessary to achieve a certain level of accuracy in your calculations.
Enabling Advanced Functionality
Did you know some advanced Excel functions rely on reference cells?
You can make data tables look good. Data Tables allow you to explore the effects of changing one or two variables in a formula.
Now that you understand the importance of this feature. You should know the exact meaning of it as well.
So, What is a Circular Reference in Excel?
A circular reference in Excel occurs when a formula refers back to the cell it is calculating, either directly or indirectly. In other words, the formula uses the result of its own calculation as an input, which creates a loop of calculations that never ends.
For example, if you enter a formula in cell A1 that refers to cell A1 itself, Excel will show an error message stating that there is a circular reference in the formula.
To break it down, let’s say I am typing a formula =A1*5 in cell A1 itself. This problem is known as Direct Circular Referencing (More on that later). This loop of calculation doesn’t match with the FOR loops in Excel.
Circular references can cause incorrect calculations, slow down the performance of the workbook, and even crash Excel.
One important information about CR – It always returns a value 0 (zero) after showing the error message.
What Are The Types of Circular References?
There are two types of circular references in Excel: direct and indirect.
1. Direct Circular Referencing
A direct circular reference occurs when a formula directly refers to the cell it is calculated in.
For example, if we select the cell A1 and type the formula =A1+B1, this is a direct circular reference because it refers back to itself. Notice that you are typing and referring to the same cell A1.
2. Indirect Circular Referencing
An indirect circular reference occurs when a formula refers to a cell that refers back to the original cell indirectly.
For example, if we select A1 as the active cell and type the formula =B1 and similarly cell B1 contains the formula =A1, this creates an indirect circular reference because each cell refers to the other in a loop. 
Difference Between Direct and Indirect Circular Reference
We have generated a table that summarizes the key differences between the two. You can get a clear idea about circular dependencies with the help of this table.
| Characteristics | Direct Circular References | Indirect Circular References |
| Definition | A Formula in a cell refers to itself | Two or more cells refer to each other in a loop |
| Loop | Involves only one cell | Involves two or more cells in a loop |
| Example | Cell A1 contains formula =A1+B1 | Cell A1 contains formula =B1 and B1 cell contains =A1 |
Common Impact: Can cause an infinite loop or incorrect results.
Common Solution: Modify or remove the formula or create a new cell. More in-depth solution is about to come in the next section.
How to Find Circular References in Excel
It’s difficult to detect the circular dependencies manually. It will take hours and hours, even days if your dataset is big.
No worries…Excel has its own solutions.
Here’s how to check for a circular reference in Excel:
- Go to the Formulas Tab from the ribbon at the top of the Excel spreadsheet.
- Navigate to the Error Checking options and click on the drop-down icon.
- Select Circular References and Excel will highlight that particular cell containing dependencies.
What if you have multiple dependencies? No worries. By clicking on that drop-down list, Excel shows all the available circular dependent cells.
There is another method through which we can check circular references. That is by Using the Status Bar.
Find Circular Dependencies Using The Status Bar
One drawback to this method is that, if you have multiple dependencies, the status bar will show only the last one. All you can do is backtrack.
Look at the image down below:
We have two CRs, but the status bar is showing only the latest one. The status bar is located at the left bottom of the screen below the Sheets tab.
Status Bar Not Showing Circular Reference?
The status bar won’t show cell addresses if the circular reference is not in the active worksheet. First go to the Error Checking Drop down tab and check which worksheet has the dependencies. After that, go to that sheet and locate the problematic cell.
How to Fix Circular Reference in Excel
To remove all the circular dependent cells, you have to first identify which type of dependencies is causing the problem.
Here are the methods to remove circular references:
1. Check For Direct & Indirect Dependency
If you have a formula that refers to the cell itself then change the cell reference. For Instance, We have a formula in cell A1 that contains the cell A1 itself. That’s why upon pressing the Enter button, Excel is showing the value 0.
Similarly, We have another formula that interrelates both cell values. In that case, we have to edit the formula so that it doesn’t refer to each other.
Solution: Cut and paste the formula to a different cell or change the cell reference.
2. Enable Iterative Calculation
Iterative calculation in Excel is a feature that allows users to perform a series of calculations until a specific condition is met. It is particularly useful when solving complex mathematical problems or when dealing with circular references in a spreadsheet.
Enable Iterative calculation in your worksheet to solve the problem. If done correctly, instead of returning a value of 0, Excel will show the actual verified result.
How to Enable Iterative Calculation in Excel?
We can achieve iteration on all platforms. If you are a Windows power user, go to File > Options > Formulas > Enable the Iterative Calculation by putting a tick mark on the checkbox.
If you are a Mac User, go to Excel > Preferences > Calculation > Use Iterative Calculation > Set Maximum iterations to 100.
Is Circular Referencing Always Bad?
Generally, Circular referencing is considered a problem because it slows down your PC. However, it’s not always bad. Sometimes, you have to allow your formulas to use iterative calculations.
To do so, Go to File > Options > Formulas > Enable Iterative Calculation > Set the maximum iteration number.
Remember – The higher the iterative value, the more time it will take for Excel to calculate on your worksheet.
So, there’s a high chance your PC is going to slow down.
Circular Reference Greyed out?
Don’t worry. If this option is grayed out, then there are two possibilities:
- There are no circular dependent cells in your workbook.
- Circular Dependencies may be present in other worksheets.
FAQ
Q: Should you avoid circular references?
A: Yes, in most of the cases, you should avoid using circular dependencies because it makes Excel slower. Also, your PC might crash along.
Q: What causes a circular reference in Excel?
A: This happens when you accidentally click on different cells while in the Formula Editing mode. While typing a formula, ensure that you don’t click anywhere else.
Conclusion
Circulating References are problematic if you don’t know what it is and how it works. It’s essential to know the importance of this feature, whether or not you should allow this feature.
Most of the cases, when you face this type of problem, you should enable iterative calculations to continue with the formula.
Other than that, just cut and paste the formula to another location to fix this error.
