A fairly annoying error in Google Sheets is the circular dependency detected error. When Google Sheets returns this error, it provides no information on why this error is happening.
In most cases the error is due to the formula being inside the range that you are referring to or your formula’s input being dependent on the output.
We will be explaining in detail what this looks like and how to fix the error.
Fix Circular Dependency Detected Error in Google Sheets
The circular dependency detected error in Google Sheets is due to one of the following reasons.
- Formula being inside the range that you are referring to
- Your formula’s input depends on the output
- Forgetting Tab Name When Using Data from Another Tab
In case you do not understand what this exactly means, don’t worry because we will be showing you detailed examples of what these errors look like and how to fix them.
1. Formula Being Inside the Range that You Are Referring to
The most common cause of the circular dependency detected error is users unknowingly placing the formula inside the range that the formula is referring to.
This is usually done when filtering a dataset like in the example below.
In the example below, we have a list of people and we are trying to filter out and show only the people who are older than 25.
So we enter the formula =FILTER(A2:D, B2:B>25) and we are given the circular dependency detected error.
The error is being caused because we entered out formula in cell D1 which is inside the range that the formula is referring to.
So how would you fix this error?
How to Fix: You can either enter the same formula in cell E2 or you can change the formula to: =FILTER(A2:C, B2:B>25).
Another time this error can occur is when summing a bunch of numbers.
In the example below we want to sum all the ages, so we enter the formula =SUM(B2:B).
This returns the circular dependency detected error because the formula includes the entire column B.
If we simply change the formula to the one below the error is resolved and we are shown the sum.
=SUM(B2:6)
2. Your formula’s input depends on the output
An example of this type of error is listed below.
The formula entered in cell C1 is =A1*B1*C1. This returns the circular dependency detected error because the output of the formula depends on the input.
This is a very simple example, that has been mentioned to make it easy to understand what this type of error looks like.
To fix this error we simply have to change the formula, so the output does not depend on the input.
In this case if the formula is changed to =A1*B1 then the issue is resolved.
3. Forgetting Tab Name When Using Data from Another Tab
Forgetting the tab name in your formula when using data from another tab is also another reason why this error occurs.
In the example below we want to filter data from sheet 1, but when entering the formula in sheet 2 we forgot to mention the tab name.
This triggered the circular dependency detected error because the formula thinks we are referring to data on sheet 2.
To fix this error we need to change the formula to include the sheet name.
=FILTER(Sheet1!A2:D, Sheet1!B2:B > 25)
Tip: Make sure you include the sheet name both in the source range (A2:D) and the filter range (B2:B). Not including the formula in either one of these ranges will trigger the error once again.