• Skip to main content
  • Skip to primary sidebar

Nerd Excel

Excel Simplified

  • Excel
  • Google Sheets
  • Word
  • Google Docs

How to Fix Circular Dependency Detected Error in Google Sheets

By: Salman Patwegar

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.

Fix Circular Dependency Detected Error in Google Sheets

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.

  1. Formula being inside the range that you are referring to
  2. Your formula’s input depends on the output
  3. 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.

Circular Dependency Error While Filtering

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).

Circular Dependency Detected Error While Filtering Fixed

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).

Circular Dependency Error While Summing

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)

Circular Dependency Detected Error While Summing Fixed

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.

Circular Dependency Input Depending on Output

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.

Circular Dependency Error From Different Tabs

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.

Related
  1. How to Fix Filter Has Mismatched Range Sizes Error in Google Sheets

Primary Sidebar

Popular Now

  • How to Calculate Age in Google Sheets
  • How to Sort by Date in Google Sheets
  • How to Generate Random Numbers in Google Sheets
  • How to Generate Random Numbers in Excel
  • How to Unprotect Excel Sheet Without Password

About | Privacy Policy | Disclosure | Terms | Contact
© Copyright 2022 @Nerdexcel.com · All Rights Reserved