It is very common to see an error with saying, “Circular dependency detected” in Google sheets. This occurs often, while using any type of formula. So, when this happens, what should you do?
Circular dependency detected error in your sheets means, the formula you are using refers to a range that has the reference cell in formula itself or you can say the formula input that you are using depends on the output.
The phrase “circular dependency” it self describe the problem what means, any dependency of referenced data of any cell if circulate like a loop, then this error occur.
The easiest way to fix this circular dependency detected error is change the reference cell in formula and ensure it not refer from the range itself. Or another way is move the formula to a new cell. ( The new cell should not use in the formula range itself).
This article has been written to painstakingly take you through the entire process of fixing this error in Google sheets. I discussed the formulae that should correctly solve the error you are facing.
Follow along carefully by reading through, and have a good one!
What Is The Circular Dependency Detected Error And How It Is Look Like?
What are the notification of a circular dependency error in your Google Spreadsheet?
It is when the cell that host the formula error displays the cell text, “#REF!”
In addition, when you select the error containing cell, there is a box that pops up and displays this message, “Circular dependency detected. To resolve with iterative calculation, see File>Spreadsheet Settings.” as you can see on image above.
At most times, you do not need to adjust your google sheet settings to fix this error. Why?
Because most of the times, by adjusting the references of the source data this error disappeared, not to mention adjusting the location where the formula is locate as I told at the stating of this article.
With the following math calculation example, it is easy to explain and understand about the error:
Question A: Multiply 20 with the answer of question A.
Obviously, this not possible to calculate, or solve because it is hard to tell the answer before solving the problem. It is a very contradicting case anyway.
To understand more clearly let’s try to take a look on 2 formulae, who are depending one another’s data as a source data. Like:
Question A: What’s the answer of ques. B?
Question B: What’s the answer of ques. A?
This is another hard to tell case, because none of these two can solve one other, since both questions are dependent on each other. Both of these questions do not have answers, and so, the case remains unsolved as it is. Question A is referring you to Question B and vice versa, hence moving in a “circular dependency”.
However, this should not confuse or make you get stuck because you will learn how to do fix it, right here.
How To Fix Simple Refereeing Circular Dependency Error
Here is a example of single cell referring itself and creating an error. The cell that is referred to by the formula is the same cell that contains the formula.
For example, the formula that is in cell D10 is referring itself to cell D10, which results to circular dependency error.
This is solved by moving the formula to a different cell E10, or by changing the source reference in the formula to make it refer to different cell.
When the formula is shifted to another cell C10, the error is automatically solved because the dependency disappears.
Fixing Circular Dependency Error In Google Sheets When Summing
When you are summing on Google Sheets, it is very common to experience “circular dependency detected” error. In the most cases of this error while summing, the SUM formula itself located in the same range that it want to sum or it’s refer the full column.
For example: there is data on B1 to B10 cell and you want to get a SUM on B11, but you used the formula =SUM(B1:B15), where B11 itself in the range of B1 to B15. So circular dependency created and showing error.
By changing B15 to B10 in the formula, we can easily solve this error.
Now it will only sum the values in the cells that are in it. Instead of summing the entire B1 to B15, it will sum B1 to B10 data.
When you enter the correct formula, everything else is instantly solved.
How To Fix Circular Dependency Error By Filtering
In the above solving method, I adjusted formula reference to fix the circular dependency error. In this method, we have to adjust the column in the reference, so that the error can be sorted.
When the formula is wrongly placed, you must encounter an error.
For example, if we have a product stock volume list in a column C that need to be worked on in range A2:C and then you place the formula (=FILTER(A2:D,C2:C>5000)) in range A2:D, you must experience an error.
To fix it, all you have to do is to place the formula correctly in range A2:C; where the prices are, so that they can be filtered out.
So the correct formula should be =FILTER(A2:C,C2:C>5000)
Fix The Circular Dependency Eith IF/THEN Statement
There are more complex situations that everyone who uses Google Spreadsheets can experience. You can decide to use two different formulae that interact, but if one formula has an error, they will both display errors, simply because they interact and refer to each other.
It can be tricky for you to fix this error, especially if you are new to it. Establishing which formula responsible for the error can tax your mind because if one formula has an error, it passes it to the other formula as well, and that results to double error.
Therefore, you have to countercheck both formulae, painstakingly, to find the error, and that is the first step that you have to take when finding the solution. When you determine the formula that has an error, fix it.
In this IF function formulae example, there is total 3 column “WorkDone? (1/0)”, “WorkDone (Yes/No)” and “WorkDone?”
In cell B3 the formula =IF(C3=”Done”,”Yes”,”No”) and in cell C3 the formula =IF(B3=”Yes”,”Done”,”NotDone”), so B3 data is deepening on C3 cell data but C3 itself deepening on B3 data that means circular dependency has created. And therefore it’s showing the error.
To fix it need to simply make the formula A3=1 in replace of C3 in cell B3.
However, this error appears when you are using multiple formulae in your Google Sheets, and that is also what makes it tricky to find the error. The safety precaution that you have to take is being very careful when you are entering the formulae. You would rather do it when your mind is fresh and not tired, stressed, or depressed.
Fixing The Circular Dependency Error By Referring To A Different Tab/Sheet
It is common to make mistakes when you refer to another sheet tab in your formula and do not include the name of that tab in the reference. If you enter formula on a different sheet/tab except the host of source data and forget to enter sheet/tab name in the formula, it will result to an error.
For example: If there is data on 2 columns in Sheet4, in A column the names of the people and in B column there hobbies.
Now if I want to make a list in Sheet5 the same data (Name and Hobby) but only people has gardening as hobby, then I can do it with FILTER formula.
If I use this formula in Sheet5, A2 cell without referring the tab Sheet4 as a source tab that means in the formula A2:B in cell A2 referring itself in the formula and as a results circular dependency has created and it will show an circular dependency error like bellow picture.
To fix this error, you have to include the source data host sheet/tab name (Sheet4) to the reference in the filter formula.
By doing so, the circular dependency disappears and shows only gardening hobbies people name.
You will now be able to filter out what you wanted in your Google Sheet. It is tricky when you try to use Google sheets without knowing how to fix the problems when they arise.
There are many tasks that you can do on Google Sheets that require calculations. Since all calculations have their respective formulae, you have to enter them correctly to succeed. However, since errors do occur, you have now learnt how to solve different errors whenever they arise.
Errors frustrating a lot, especially when they pop up and you do not know where you got it wrong. All these formulae are accurate, and they solve the problems satisfactorily. Every formula is applied in specific situations.
When you get used to Google Sheets, you will become an expert in solving all errors. You can even try out to practice and see whether you can solve different kinds of Google Sheet errors.
You may love to read our few more Google Sheets tutorials on:
- How to Use the Find and Replace Function in Google Sheets
- Microsoft Excel Vs Google Sheets
- How to use Google Finance Function in Google Sheet
- Google Sheets Formulas Not Working
- Does Google Sheets Have Macros?