How do you fix a circular reference error in a workbook?

The circular reference error in Excel, is a very common error that can occur when using almost any formula. When you see the circular reference error displayed in your Excel spreadsheet, this means that your formula is referring to a range that contains the formula itself, or in other words when the formula input, is dependent on the output.

To fix the circular reference in Excel, make either of the following changes to your spreadsheet:

1. Move your formula to another cell that is not contained within the range(s) that the formula refers to
2. Or, adjust the reference in your formula so that it does not refer to a range that contains the formula itself

There are several different ways that a circular reference may display an error / warning in Excel:

1. Some versions of Excel will display a pop up warning, that has a message like this: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells"
2. In some versions, the warning may say this "Careful, we found one or more circular references in your workbook that might cause your formula to calculate incorrectly. FYI: A circular reference can be a formula that refers to its own cell value, or refers to a cell dependent on its own cell value.
3. In some versions of Excel (such as Excel online), the cell will sometimes not display a warning, and will simply calculate incorrectly, where the number "0" displays as the formula result
4. In some cases, such as when using the FILTER function, a circular reference can cause an "Empty Array" warning / error

Since I used Excel Online to create these examples, most of my examples show the third error in the list above, where the cell shows the number 0. The error may appear differently on your version of Excel, but the cause of the error as well as the solution are the same across the board.

This article shows how to fix a circular reference error in Excel, but click here if you want to learn how to fix the circular dependency error in Google Sheets.

Circular reference errors can also occur when two formulas refer to the range that the other formula resides in, even if the formula does not refer to itself (i.e. its own location). In the same way that a single formula's input cannot be dependent on data that is determined by its own output, two formulas cannot simultaneously be dependent on each other's output. This can cause a confusing situation where one wrong formula causes two formula errors.

In this article I will go over several different examples of how you might experience this reference error in Excel, and I will also show you how to fix the error in each situation.

When a circular reference error occurs in your spreadsheet, the cell that contains the formula error will either display the number 0, or Excel may display a warning / error, or both.

Below are two examples of the circular reference warning that you might see:

When your formula is inside of the range that it is referring to, this means that the formula input is "dependent" on the output, which is not possible to calculate, and causes an error.

Question 1: Divide 10 by the answer to question 1.

This is impossible to solve because the output/answer cannot be known before the problem is actually solved.

Or in the case of two formulas that refer to each other's output/location, here is another analogy. This is like being given the two following math problems:

Question 1: What is the answer to question 2?

Question 2: What is the answer to question 1?

Again, this cannot be solved. The answer to each question is dependent on the other, which makes your mind run in circles… hence the phrase "circular reference".

Don't let this make you feel confused, because that's the point is that this logic causes an error. All you need to know is why it happens, and how to fix it.

So let's go over some actual examples of resolving this circular reference error in your Excel spreadsheet.

Let's take a look at the most simple example of the circular reference error.

Below, the image shows a formula that simply refers to a single cell. However, the problem is that the cell that formula is referring to, is the cell that the formula is entered into (the formula in cell A1, is referring to cell A1).

As you can see, this has caused a circular reference error.

The following formula causes an error, when entered into cell A1:

=A1

To fix the error, we can either move the formula to another cell, or change the reference in the formula so that it refers to another cell.

In this case we will change the cell reference to cell B1.

As you can see in the image below, this adjustment has fixed the circular reference error.

The following formula has been adjusted and resolves the error:

=B1

Now, cell A1 displays the text that is in cell B1.

Fixing circular reference when summing

A common situation where you might experience the circular reference error, is when you are summing in Excel. This will happen most often when your SUM formula is in the same column that it refers to, and when the formula reference captures the entire column.

The image below shows a simple SUM formula, that attempts to sum the numbers in cells A1 through A5.

But as you can see, the SUM formula refers to the range A1:A10. Since the SUM formula is entered into a cell within column A (A6), and the range A1:A10 contains cell A6 (where the formula is), this causes a circular reference error.

The following formula causes an error when entered anywhere in column A:

=SUM(A1:A10)

To fix this error, we will adjust the reference in the formula so that it only sums the values in the cells above it.

So rather than trying to sum the entire column, we will designate an ending row in the reference (a row that is above the sum formula).

To do this, simply change the sum range to A1:A5.

This fixes the circular reference error, as shown in the image below.

The following formula has been adjusted and resolves the error:

=SUM(A1:A5)

Now the SUM formula in the image above, successfully sums cells A1 through A5. (1+2+3+4+5=15)

Fixing circular reference when filtering

In the last example we had to adjust the rows in the formula reference to fix the circular reference error, but let's take a look at an example where we will adjust the columns in the reference to resolve the error.

In this example, let's say that we have a list of school supplies and their prices entered into a spreadsheet, and we want to filter the data with a formula so that a list of items costing more than $1 is displayed.

As you can see in the image below, the FILTER formula has a circular reference error. This is being caused by the reference to the source range, which is one column too wide (considering where the filter formula has been placed).

If the formula refers to the range A2:D, which contains column D, the formula cannot be placed in column D.

The following formula causes an error when entered into cell D2:

=FILTER(A2:D10,C2:C10>1)

To fix the error that is shown in the image above, change the range that refers to the source data from A2:D10, to A2:C10.

After making this adjustment, the error is fixed, and the FILTER formula works properly.

The following formula has been adjusted and resolves the error:

=FILTER(A2:C10,C2:C10>1)

Now the school supplies are being filtered to display a list of items that cost more than $1.

This content was originally created and written by SpreadsheetClass.com

Fixing circular reference with if/then statement

Now let's take a look at a more complex example, that could happen to anyone who uses formulas in their spreadsheets. In this example, there are two different formulas that are interacting, and because one of them was set up incorrectly, both are displaying an error, due to the fact that each are referring to (dependent on) each other.

(For more explanation on why this happens, see the top of this article)

When an error happens like the one that is shown in the image below, it can sometimes be hard to determine which formula has the mistake, because of the double error that it causes. As in any troubleshooting scenario… the best thing to do is to start from the beginning, and trace your way through the data/system until you find the mistake.

So here is the scenario in this example: Column A indicates the completion of a task with 1's and 0's. The formulas in column B were intended to refer to the data in column A, and to display the text "Yes" or "No", depending on if each cell in column A had a number 1 or a number 0. Then, column C refers to the cells in column B, and displays the words "Complete" or " Not Complete", depending on whether each cell in column B says yes or no.

In short, if cell A3 contains the number 1, then cell B3 should say "Yes", and cell C3 should say "Complete".

But the problem is that the formula in cell B3… instead of referring to the 1's and 0's in column A, the sheet's creator made a mistake, and referred to column C (which in turn is referring back to it). This creates a circular reference error, in BOTH formulas, even though technically only one of the formulas was set up incorrectly.

This type of mix up is common when using lots of formulas in your sheets, and especially when you have been creating all day and are tired.

To fix this formula, which will fix both of the circular reference errors, follow the instructions listed below the image.

The following formula causes an error when entered into cell B3, due to another formula in cell C3 that refers to cell B3:

=IF(C3="Complete","Yes","No")

In this case, to fix the error, it is more than just a matter of changing the reference in the formula, because the whole formula was written incorrectly by mistake. So remember that the formulas in column B, should display the word "Yes" in each row/cell if there is a number 1 in the adjacent cells in column A  (and the word "No" if there is a 0 in the adjacent cell).

The corrected logic for the formula in cell B3, is as follows: If cell A3 equals 1, then display the word "Yes", and if not, then display the word "No".

The following formula has been adjusted and resolves the error:

=IF(A3=1,"Yes","No")

Now both of the formulas are working properly, and both of the circular reference errors have been fixed at the same time, by correcting one formula.

Now, column B refers to column A, and then column B refers to column C, as it should be. The formulas are no longer simultaneously dependent on each other's output.

Fix the circular reference error when referring to another tab

One more very common way of running into the circular reference error, is when you are referring to another tab in your formula, and you forget to include the tab name in your reference.

The data below shows a list of clothing items and their sizes listed in a spreadsheet. We want to filter the data by using a formula in another tab, to only show items that have the size "Medium".

The picture below shows a FILTER formula that is entered in cell A2, on a different tab than the one that holds the source data shown above.

The problem is that the tab name was left out when the formula was entered.

Since the source range is A2:B14 and the formula is in cell A2, this means that the formula is referring to itself. Or in other words, the cell that the formula is entered in, is within the range that the formula refers to. This causes a circular reference error.

The following formula causes an error when entered into cell A2:

=filter(A2:B14,B2:B14="Medium")

To fix this error, simply add the tab name to the references in the filter formula.

The reference to the source range will be 'Another Tab'!A2:B14 (Apostrophes must be added before and after the tab name reference, when there is a space in the tab name).

The following formula has been adjusted and resolves the error:

=filter('Another Tab'!A2:B14,'Another Tab'!B2:B14="Medium")

After adding the tab name to the references in the FILTER formula, the circular reference error goes away, and the formula filters properly, displaying a list of clothing items that are "Medium".

Now you know how to easily fix this error whenever it pops up in your spreadsheet!