Google Sheets is the most powerful and easy-to-use web-based free spreadsheet tool. But that doesn’t mean you’ll never run into problems when using it. Because it has so many features, it also has a lot of potential errors you can run into.
Frequently, users may run into the circular dependency detected error, which can be super annoying to deal with because Google Sheets shows this error without indicating why it occurs.
Don’t worry. This article will walk you through how to fight your way through this error by showcasing why it occurs and how to fix it. Read on to learn everything you need to know about circular dependency in Google Sheets.
Table of Contents
What Is Google Sheets Circular Dependency?
You can face the error “Circular dependency detected” when using virtually any formula. When you encounter this error, the formula used usually exists in the range where the calculation takes place, which means that its input is directly correlated with the formula itself.
This ends up creating a paradox-like situation where the formula needs an input to work, but the input is the formula’s output, which halts the formula’s functioning entirely.
Sometimes when there is data in another sheet, the user may forget to connect to that sheet in the formula, which can cause the circular dependency error.
When the circular dependency error occurs in Google Sheets, the error cell will show a small red triangle in the top right corner, and the text will change to say #REF!
When you hover the cursor over the cell which has the error, it shows the following message:
“Circular Dependency detected. To resolve with iterative calculation, see File>Spreadsheet Settings.”
Changing Google Sheets’ iterative calculation requires you to change your settings. Instead, you can resolve circular dependency by changing the formula in the spreadsheet.
When Can Circular Dependency Error Occur?
If you’re facing this error, then there are three leading causes. These include:
- The range selected contains the formula itself
- The output of the formula is entirely dependent on the input
- The tab name is omitted in the formula when selecting the data of another tab
This could also be a problem if you have messed with the iterative calculation Google Sheets settings before. Reset them to normal by navigating to File>Settings and turning iterative calculation off.
A few of these cases are explained in detail below to instantly help you know where the problem lies when you get these error notifications and how to fix them.
1. Selected Range Contains the Formula
Many people confuse the range of the cells to put in the formula. For this example, we want to calculate the workers’ total wages in an office. We use the SUM formula, but the data is only in the cells ranges B2 to B5.
However, the formula uses the range from B2 to B6. B6 is the formula’s cell, the root of the problem.
While the cell charges B2 to B5 are fine because they contain the required values, B6 is not as it’s the cell containing the formula.
To fix this, we need to ensure either the formula exists in a different cell or change the cell range so that it doesn’t lie within the range that contains the formula.
The Fix
Here is how to fix circular dependency detected in Google Sheets:
The first method is changing the cell range so that the formula doesn’t contain the cell the formula is in. In the example above, we reduced the range to B2:B5—the formula in cell B6.
2. The Output Depends on the Input
It’s common to encounter the circular dependency detected error when using multiple formulas in a spreadsheet that rely on each other. Often you may encounter this error on all the formulas together.
In this example spreadsheet, we have a few items stored and checked to see if they are low on stock or not. If their count is below a specific limit, a message prompts the user to restock the items.
In this picture, we knowingly remove the value needed in the formula contained in cells C3 to C5. As you can see in the cells D3 to D5, the Restock tab’s values depend on the output of the values in C3 to C5. So when that formula fails, the cells in the other column also fail.
The Fix
To fix this error, we first need to ensure that the first formula in the sequence works before heading to the next. In this case, we need to make sure the results in the Amount column work.
Often it’s the first formula that’s causing the other formulas to fail.
The formula used in the Amount column is the culprit here because we failed to specify the limits. The formula used there was:
=IF(B3>,"High","Low")
It works if we change the formula to:
=IF(B3>40,"High","Low")
Now you can see that the Restock? Column’s formulas work fine now.
To boil it down, if one formula relies on another, make sure the previous formula gives a result first.
3. Tab Name Is Neglected
Neglecting tab names is another cause for many users to see the circular dependency detected error when a different tab needs to be in the formula. It happens when you forget to include the tab name in the reference.
In this case, we are using the FILTER function to filter to return the rows and columns in a range if it meets specific criteria. However, the data to be shown exists in another tab.
As the data we need to filter through is contained in another tab, it essentially exists in a separate spreadsheet.
The Fix
To fix this error, we need to specify the spreadsheet’s name in the formula. We add the tab’s name in the formula to specify the tab in which the spreadsheet exists. The formula used before was:
=FILTER(A2:A5,B2:B5>4)
Now, we add the name of the tab to the formula, which now is:
=FILTER(Data!A2:A5,Data!B2:B5>4)
You’ll also need to do this for other Google Sheets formulas like VLOOKUP.
Frequently Asked Questions
What Is a Circular Dependency Detected?
The circular dependency detected error often occurs for three reasons. Either:
- The formula contains the cell in which that formula exists
- The formula’s output is dependent on the formula’s input.
- The formula requires data from another spreadsheet tab that the user fails to specify
How Do I Fix Warning in Circular Dependency Detected?
- Ensure your formulas are working correctly
- Double-check the values inside them
- If multiple formulas exist, it’s best to check the parent formula. Otherwise, child formulas are affected automatically
- Ensure there are no dependencies in the formulas
What Does Circular Dependency Detected Mean on Google Sheets?
As the name suggests, circular dependency creates a loop where variables are dependent on each other. Fixing this error relies on breaking the cycle and ensuring that formulas have a defined end.
Removing The Circular Dependency Detected Google Sheets Error
Encountering the circular dependency detected error in Google Sheets can be highly frustrating, especially if you don’t know where the error came from. We hope this article helped provide a detailed explanation of where the error originated from, how to eliminate it and how you can avoid it in the future.
If this didn’t fix your issues, you may also have additional issues with your formula. Check out our article on Google Sheets formula parse errors to help you out.
