Circular references in Excel can be quite a source of annoyance. If you’ve come across this error, you’re not alone.
Numerous Excel users face this problem every day, but the good news is that it’s solvable.
In this tutorial, we will discuss what a circular reference means, what happens when there’s a circular reference in your Excel spreadsheet, and how to find them and fix circular references in Excel.
In some exceptional cases, you might actually want Excel to allow circular references.
So, we will also discuss how you can enable/disable circular reference warnings.
What is a Circular Reference in Excel?
A circular reference is a formula that tries to access its own cell location either directly or indirectly, causing an infinite loop.
This may either be accidental or intentional.
For example, if you type the formula =A1 in cell A1, then this creates a circular reference, since your formula is trying to access its own location.
It’s also a circular reference when you use the following formulas in cell A1:
=A1*2 or =IF(A1=5, “yes”,”no”).
Since we need the result in cell A1 and we are entering the formula in cell A1, we can not refer to the same cell.
In almost all cases, your formula in the cell would be referring to other cells in the spreadsheet.
Two Types of Circular References in Excel
Circular references can be of two types:
- A direct circular reference
- An indirect circular reference
A direct circular reference is what we just saw – a cell directly trying to access its own location (for example, entering =A1 in cell A1 itself).
A very common direct circular reference is when we try to sum up cells in a column and accidentally end up including within the sum formula, the same cell containing the formula.
For example, in the screenshot below, we are trying to sum up all the cells of column A, which also includes cell A6 (the cell containing the sum formula).
In this way, the formula in A6 is trying to refer to itself, and thus we see a circular reference warning message.
An indirect circular reference takes place when a cell is trying to access a cell that in turn is referring back to itself, either directly or indirectly.
For example, in the screenshot below, cell A1 is referring to cell B5, but cell B5 is, in turn, referring back to cell A1.
So indirectly, cell A1 is referring back to itself, causing a circular reference.
The following screenshot shows another example of a circular reference.
This time cell A1 refers to cell B5, which in turn indirectly refers back to cell A1 (through C3).
What Happens when there is a Circular Reference in Excel?
When you force a formula to reference its own cell, Excel shows the following error message:
“Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly.”
This is because a circular reference causes an endless loop, as Excel keeps returning to the same cell over and over.
If you have large datasets that involve multiple formulae referring to multiple cells in multiple sheets, you will find your work getting increasingly complex.
This may result in an increased risk of errors, invalid data, or incorrect formulae.
Besides the error message, when you have a circular reference in your sheet, you can also see a circular reference warning in the status bar below the worksheet.
The status bar displays only the latest circular reference. You can use this warning to backtrack and resolve the conflict.
In some cases, you can also see arrows pointing in the direction of the references to help you clearly see why the references are cycling back and forth.
For example, in the following sheet, there are two different types of circular references.
To the right side, there is a direct circular reference, where G6 refers to H7, which refers back to G6.
Notice that Excel shows a two-way arrow between the two cells, to tell you that there is a circular reference here.
Similarly, on the left side, there is an indirect circular reference too, where cell A2 refers to cell B5, which refers to cell C3, and C3 refers back to cell A2.
Here, Excel shows 3 one-way arrows in a triangular shape to mark that there is a circular reference between these three cells.
You can also see that the status bar includes the status ‘Circular References: H7’, telling us that the sheet includes one or more circular references.
Although there are two different circular references in this sheet, the status bar shows a reference to only the last circular reference.
How to Find and Fix Circular References in Excel?
To resolve a circular reference issue in Excel, you need to first detect it and find its origin.
Even though you get a warning as soon as a circular reference occurs, it is sometimes not possible to detect the error as soon as it occurs, because there might be more than one circular reference in the cell, or you might be looking at a sheet that has been prepared by someone else.
Resolving a circular reference is especially difficult when you have a large or complex dataset that involves multiple formulae referring to multiple cells.
Such datasets have an increased risk of errors, and these errors are often difficult to trace.
However, there are ways to detect the source of the error. Once you know which cell or formula the error originated from, it becomes much easier to fix it.
Here are two ways to find circular references in Excel:
- Using Excel’s Error Checking menu
- Using Excel’s Trace Dependents and Precedents feature
Both features are under the ‘Formula Auditing’ group of the Formulas tab.
Using Excel’s Error Checking Menu to Find Circular References
Excel offers the Error Checking menu to help you quickly detect and resolve different types of errors.
As such, you can use this menu to find all the cells in your sheet that have a circular reference too.
Let’s say you have the following dataset, where cell A2 refers to cell B5, but cell B5 refers to cell C3, which in turn refers back to cell A2.
In other words, we have an indirect circular reference situation here, where cell A2 is, in essence, referring back to itself.
Let us see step-by-step how to use Excel’s Error Checking menu to find and resolve this error:
- Click on the Formulas tab
- Click on the Error-checking dropdown menu (under the Formula Auditing group).
- From the dropdown list that appears, hover over ‘Circular References’.
- You should see the list of cells that comprise the last created circular reference in the sheet.
- Click on any one of the cells in this list.
- You should see the corresponding cell activated so that you can go ahead and resolve the issue.
Now you can either choose to remove the cell reference, change the formula or move it to some other location, whatever best solves your circular reference problem.
Using the Trace Dependents and Precedents Feature to Find Circular References
Another method to help you trace and resolve circular references is by using the Trace Dependents and Precedents feature.
Tracing dependents and precedents lets you find the path that connects the references with a line drawn between cells causing the circular reference.
The Trace Precedents feature lets you trace back to cells that the current cell references (or depends on). The feature draws a line showing the cell(s) affecting the current cell.
For example, in the screenshot below, when cell A2 is your active cell, the Trace Precedents feature shows the arrow pointing from cell B5 to A2, which means that ‘A2 depends on B5’ or ‘B5 is affecting cell A2’.
The Trace Dependents feature lets you locate the cells that are dependent on the active cell. The feature draws a line showing the cell(s) that are affected by the current cell.
For example, in the screenshot below, when cell A2 is your active cell, the Trace Dependents feature shows the arrow pointing from cell A2 to C3, which means that ‘C3 is dependent on A2’ or ‘A2 is affecting cell C3’.
Why is this important in resolving circular references?
Well, we need to understand where the error in a cell originates from, so we need to trace it back to the cell’s precedents.
We also need to know which other cells are being affected by the problematic cell, so we need to trace the erroneous cell’s dependents.
The graphical view that this feature gives us (with the arrows) makes it easier to quickly trace the path of a circular reference, making it quick and easy to resolve the conflicts.
To access this feature, follow the steps shown below:
- Check the cell references next to the ‘Circular Reference’ status in the status bar.
- Click on the cell referenced to activate it.
- Click on the Formulas tab.
- Click on Trace Precedents (under the Formula Auditing group) to track the cells that the active cell depends on. Click this button again and again till you can see an actual circular reference path. Alternatively, you can click on Trace Dependents (under the Formula Auditing group) repetitively to track the cells that depend on the active cell.
Once you can see the full path of the circular reference, you can go ahead and resolve the conflict by addressing the references one by one.
Note: You can use the shortcut ALT+T U D to trace dependents and ALT+T U T to trace precedents.
How to Enable/Disable (or Allow) Circular References in Excel
Circular references, though problematic, can actually be required in some situations. That is why you only get a circular reference ‘warning’.
It is not considered an error by Excel. This means you are allowed to ignore the warning and continue working on your sheet if you want.
If your application actually needs to use a circular reference, or you just want to keep working without being bothered by the repeated circular reference warnings, you can choose to enable Iterative calculations.
Iterative calculations are those that run over and over using previous results. They are often used to find the solution to mathematical problems.
For example, when you are trying to compute the Future value of an investment, you need to repeatedly update your initial value with the computed total value at the end of a given time period.
To compute the total amount at the end of each month, your formula is:
Your computed total value here is computed using a formula that requires the initial value at the beginning of the time period, while the initial value has to keep getting updated with the total amount each month.
This gives rise to a circular reference, but one that is required.
To avoid going into an infinite loop, you need to limit the number of iterations.
For example, in this case, since we want to find the Future value at the 12th month, we can simply limit the number of iterations allowed to a maximum of 12.
Another example is when you need to have an automatic timestamp, that computes the current date and time whenever new information is added to a given cell.
In such cases, you can use a formula like the one shown below, which checks if A2 is empty, and if so, it computes the current timestamp.
If a timestamp already exists in A2, it does not recalculate.
This kind of circular reference is usually required when you’re using volatile functions like NOW, which recalculate every time the sheet loads, or when there is a change in the sheet.
The above formula simply ensures that the NOW function computes just once and then the timestamp remains that way after that.
The above computation takes place just once, so the circular reference is pretty much harmless.
Under normal circumstances, Excel will not allow the iterative calculations when the above formula is used. It will throw a circular reference warning and return a 0.
However, if you enable iterative calculations, the above formula will be allowed to have a circular reference and you will get the desired result.
If you want to enable iterative calculations, here are the steps to follow:
- Click on the File tab.
- From the list on the left sidebar, click on Options.
- This opens the Excel Options dialog box.
- Click on Formulas from the list of categories on the left.
- Under Calculation Options, check the box next to ‘Enable iterative calculation’.
- Specify the ‘Maximum number of iterations’ and amount for ‘Maximum change’ according to what you need. If you’re not sure, then you can leave them at the default values.
- Click OK.
Iterative calculations should now get enabled and Excel will allow you to have circular references.
Whenever you want to disable iterative calculations, repeat the same steps and uncheck the box next to ‘Enable iterative calculations’ in step 5.
In this tutorial, we discussed circular references, why they happen, and how to fix them.
We also showed you some cases where they might be useful and how to get Excel to allow circular references in your sheets.
Although circular references might be advantageous in some rare cases, we suggest that you avoid them as far as possible, either using VBA or some other alternatives.
Some Other Excel tutorials you may also like:
- SPILL Error in Excel – How to Fix?
- Why does Excel Open on Startup (and How to Stop it)
- How to Start Excel in Safe Mode (3 Easy Ways)
- How to Calculate NPV in Excel (Net Present Value)
- Can’t Type In Excel: 6 Possible Reasons and Solutions!
- Why is Merge and Center Grayed Out?
- How to Break Links To External References in Excel?