Many people find it difficult to find and fix a circular reference in Excel.
If you’re one of them, don’t worry as you’ve come to the right place.
As someone using Excel for years, I’ve dealt a lot with circular references.
So I’ll elaborately explain circular references in this post, including how to locate them effectively.
Let’s dive in.
Check out the easiest way how to add a trendline in Microsoft Excel.
What is a Circular Reference in Excel?
Suppose your Excel sheet has a formula that you tried to insert, but it isn’t working for some reason. Instead, it provides a warning regarding a circular reference. You may not know what it is.
A circular reference is like a loop where you use the value of the cell you’re editing inside the formula of that same cell.
For example, if you want to edit A1 and you apply the formula =A1+10 to A1, you will receive an error message that goes like this:
There might be one or more circular references in your worksheet where a formula refers to its cell either directly or indirectly. This might cause them to calculate incorrectly.
Try removing or changing these references or moving the formulas to different cells.
If you click on OK on the error message box, the cell will have a value of 0 in this instance. You can also click Help instead for more information. 
Excel circular references have the potential to create an endless loop that would greatly slow down worksheet calculations. This is why Excel shows you the warning and you need to be careful to avoid circular references.
I previously mentioned that a circular reference could be both direct and indirect. The example I showed you above was of the direct kind. In a direct circular reference, the formula refers to its own cell.
In an indirect circular reference, you are trying to edit the value by applying a formula where one of the cells referenced depends on the value of the editing cell
This may sound a bit confusing. So let us take a look at a simple example by creating a circular reference.
Let us specify a value in B1. In this example, I will use 10. Then I apply the formula =B1+10 to C2. After that, I apply the formula =C2+15 to D3. So far, so good, right?
However, if you attempt to edit B1 once more and, for example, try to use the value in D3 in a formula for B1, Excel won’t accept this unwanted situation. you will receive the circular reference warning . 
In this case, B1 is not directly referencing B1. But the B1 cell is referencing D3, which is referencing C2, which is referencing B1. So, the reference here is indirect.
Note: In many cases, a circular reference message error occurs by accident. In a few circumstances, though, employing a circular reference in an Excel workbook is acceptable since it offers a quicker and more elegant solution. It could be the only solution as well.
How to Find a Circular Reference in Excel
Finding circular references is usually the trickiest challenge you have to face when dealing with them, especially when working with large data sets.
You could be working with thousands of cells or even more. In such a situation, you may need Excel’s assistance to identify which cell carries the circular reference.
Circular references could be intended, unintended, and hidden. Hidden circular references are the most difficult to find manually. This is why it is better if you learn how you can easily find all kinds of circular references in Excel. This section will elaborate on how to locate and remove them.
You may also like to read how to delete a sheet in Excel.
Follow these steps to find the circular references in Excel:
- Go to the Formulas tab if you can’t find the problem.
- Go to Error Checking afterward.
- Click on Circular References and then click on the first cell listed in the submenu.
- Review the cell’s formula.
- Click on the next cell in the Circular References submenu if you cannot establish whether the current cell is the source of the circular reference.
- Repeat all the steps until the status bar no longer displays circular references.
The status bar at the bottom of the sheet will also show the circular reference when you click on the cell that contains it. Let’s take a look at an example to understand the process better.
First, let’s replicate the formulas used in the examples above for direct and indirect circular reference. Add one more circular reference to make it clear. Apply the formula = A2+5 to A2. This is a direct circular reference.
Knowing how to find circular reference formulas in Excel will be helpful in such cases. This combines applying the steps mentioned above and turn on Show Formulas.
Display all the formulas to ensure you have applied all the formulas accordingly. Click on the Formulas section. Then click on Show Formulas under Formula Auditing. With this, the cells containing formulas will reveal the formulas. You’ll get an overview of what you did.
Now, you have to follow the steps mentioned above accordingly. Click on the drop-down menu near Error checking under Formula Auditing on the Formulas tab. Click on Circular References and then click on the first cell listed in the submenu. 
When we select a cell, notice that the first circular reference found is of the direct kind. Fix it. I randomly entered the value of 20.
We then repeat the same process to find another circular reference. This time, A2 has been located. I randomly entered the value of 15 in A2 to fix circular reference. Notice that this was also a direct circular reference
We repeat the process once again for the final circular reference. This time we have located an indirect circular reference path. You can fix the problem in many ways. I will show you the simple route of making the value of one of the cells independent of the other cells. 
I have made B2 independent of any other cell. This should fix the circular reference in the selected cell.
After this, you can check for circular references again, but you won’t find anything. 
Finally, click on Show Formulas again to turn off the formula in cell display. You will be able to see all the stored cell values again.
So, what did you notice? Microsoft Excel found all the direct circular references before the indirect ones. Excel prioritizes finding direct circular references first. For indirect circular reference, the entire indirect path is shown. But you do need to alter every cell to remove circular references in Excel.
You can move between circular references in your spreadsheet by going to the Formulas tab and clicking on Trace Precedents or Trace Dependents. It’ll reveal the blue arrows. The arrow shows circular references between two cells or more. Then you’ll need to double-click on the arrow to move between cells.
Also, related to this guide is how to move a row In Excel Easily.
How to Enable Iterative Calculations in Excel
You may intentionally want to use a circular reference for a specific task for a specific task. Circular references in these cases will not show as a warning only if iterative calculations are turned on. The iterative calculation is turned off by default in Excel as in most cases you want to avoid iterative calculation.
Follow these steps to turn on iterative calculations in Excel 2010 onwards:
- Click on File on the top left of your screen.
- Click on Options
- Click on Formulas. Then enable iterative calculations by clicking on the checkbox.
- Click on OK to finish the process.
If you want to disable iterative calculations, just follow the same steps but uncheck the checkbox instead if it is already turned on.
The Maximum Iterations box indicates the maximum number of times Excel should recalculate the formula. The calculation takes longer the more iterations there are.
The Maximum Change box indicates how much the computation results can vary. A smaller value will give you a more precise result, but the time it will take will be longer too.
Some more guide on how to group rows in Microsoft Excel.
Conclusion
You can easily find a circular references in MS Excel by using the built-in option. Excel will locate direct circular references first. You must manually fix all the problematic cells you have managed to find.
If you want to use circular references intentionally, you can enable iterative calculations by using the steps in this tutorial. You can also disable circular references using the same steps.
Do you have any other queries? If the answer is yes, comment below!
