Learning to diagnose and fix error messages is an important part of using spreadsheet programs like Google Sheets. Everyone gets errors eventually.
One common type of error that you may come across is the circular dependency error.
In this tutorial, I will explain what circular dependency detected means, and how to fix this in Google Sheets.
Table of Contents
What Does Circular Dependency Detected Mean?
Circular dependency detected means that there was an error in your formula. It is a type of #REF! error that means Google Sheets is unable to calculate your formula because of the circular dependency.
If you have a circular dependency error, you will see a message like this in your spreadsheet:
A circular dependency error is caused by the input of a formula depending on the output of the formula.
To illustrate what I mean by this, let’s take a look at an example:
In the example above, I have two formulas that are returning a circular dependency #REF! error.
The formulas are =A2+C2 and =B2+C2.
The problem with these formulas is that:
- B2 depends on the results of C2
- C2 depends on the results of B2
In other words, the input of these formulas depends on the output of the formulas.
This results in a circular dependency error.
How to Fix a Circular Dependency Error
Now that I have covered the basics of what a circular dependency error is, let’s take a look at how to fix some of the common causes of them.
Formulas Depend on Each Other
One of the most common causes is the formulas depend on the results of each other like in the example that I showed previously:
The only way to fix an issue like this is to make one of the formulas not depend on the results of the other formula.
So in this example, I would fix this by removing one of the formulas and having a value there instead
Formula Depends on Itself
Another common cause of these kinds of errors is that you have a formula that references itself in the formula. So the formula can’t calculate because it needs to use the output of itself as an input to calculate the formula.
In the example above you can see that there is an error in cell B2. This is because the formula in B2 (A2+B2) needs the result of B2 to calculate. This will cause a circular dependency error.
Here is another example:
In this example, I have a basic function setup that references itself in the range of the function (B3).
To fix these kinds of errors, make sure that your formula does not include the cell that contains the formula in the parameters of the formula anywhere.
In this situation I will fix the formula by removing the reference to cell B3 from the function:
Most often these kinds of errors are the result of simple mistakes in the formula that are easy to fix. Once you understand what causes these errors, you should be able to look at the parameters used in your formula and fix them.
We hope you found this helpful!
More Google Sheets Tutorials:
Formula Parse Error