In this article, we would have a look at what is circular dependency in Google Sheets and why it occurs. We would also see how to rectify it based on different conditions. But before proceeding, we should know what exactly is circular dependency in Google Sheets.
What is a circular dependency in Google Sheets?
Circular dependency is a type of error in Google Sheets. As the name suggests and as explained by the diagram below, it occurs when a resource is dependent on another resource which is, in turn, dependent on the first resource.
In the context of Google Sheets, let’s say you are in Cell A and you use a formula which is dependent on the value of Cell B. But cell B also uses a formula which is dependent on cell A’s value. Hence this creates a loop which confuses the Google Sheets compiler and throws an error message.
When Google Sheets detects a circular dependency error it displays an error message which looks something like “#REF!”. If you hover on it, it shows the message “Circular Dependency detected. To resolve with iterative calculation, see File>Spreadsheet Settings.” You don’t actually have to follow the commands and see the spreadsheet settings.
This can be confusing at first since Google Sheets does not exactly tell you why the error has occurred. Hence, we have compiled a list of common causes and reasons that might have led Google Sheets to throw this error.
Removing Circular Dependency
We will have a look at the common causes behind this error and then subsequently discuss the fixes for that particular cause.
Value depends on each other
As we explained in the section above circular dependency in google sheets is caused by resources referencing each other, let’s have a look at an example.
Here, the formulas for B2 and C2 have been written below the respective cells. As you can see, since both the cells depend on each other, a circular dependency has been detected. The fix to this is, that you have to make a linear relationship between the data. One of these fields should have independent data. A possible fix to this might look like this.
The selected range contains the formula itself
Let’s say we have another table this time which looks like this. It contains the marks of a student per subject and the total marks.
We accidentally set the range from B2 to B7. Here, B7 itself contains the formula. In a way, this makes a self-loop in the table. The fix is pretty simple, just change the range from B2:B7 to B2:B6.
As you can see, most of these cases are more logical errors rather than syntactic ones.
Referencing from another tab without using the tab name
The following figure depicts another sheet in the same workbook. We want to count the number of subjects with marks greater than 50 for the student we mentioned in the previous section. Everything looks right, but we forgot to reference the tab name. Hence it threw an error.
The obvious way to rectify this is to add the tab name to the formula. Tab name is basically the name for the sheet you want to access the range in.
Conclusion
In this article, we read about circular dependency in Google Sheets. We saw what causes it and different cases which commonly occur. Finally, we saw how to remove circular dependency in google sheets for all the cases.
See Also
If you want to know more formulas and functions in Google Sheets, look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.
How To Calculate Standard Deviation In Google Sheets: We will learn how to calculate standard deviation in Google Sheets