The circular reference error in Excel, is a very common error that can occur when using almost any formula. When you see that the circular reference error keeps popping up 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 one the following changes to your spreadsheet:
- Move your formula to another cell that is not contained within the range(s) that the formula refers to
- Or, adjust the reference in your formula so that it does not refer to a range that contains the formula itself
- Or if two formulas are referencing each other / dependent on each other, change the references for one of the formulas so that both formulas are not referring to each other
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.
Here is a basic example of finding and fixing a circular reference error in Excel:
Let's say that in cell A1, you enter the formula =A1+A2. This will cause a circular reference, because the formula in cell A1, is referring to cell A1, i.e. it is referring to itself. Fixing this is easy. Simply move your formula to a different cell (a cell that the formula does NOT refer to), or you can change the cell reference in the formula so that you are no longer referring to cell A1 / no longer referring to the cell that the formula is in.
Further below are even more examples of fixing the circular reference error when using a variety of different functions, but for now let's go over the two main ways that a circular reference presents itself.
The two main types of circular reference errors:
- When the selected range (or cell) contains the formula itself
- When two or more formulas depend on each other
The selected range contains the formula (or selected cell contains the formula)
When the range of cells that a formula refers to contains the formula itself, this will cause a circular reference error. This also goes for formulas that contain references to individual cells. If your formula refers to a cell that the formula itself is in, this will cause a circular reference error. To fix this we either move the formula to another cell that is not contained within the range(s) that the formula refers to, or adjust the reference in the formula so that it does not refer to a range that contains the formula itself.
In this example we will change the reference / range in the formula to fix the circular reference error.
Below we have a SUM formula in cell B5, which is referring to the range B2:B100. Since the cell that contains the formula is inside the range that the formula refers to, this will cause a circular reference error.
The problem: The formula in cell B5 refers to the range B2:B100 (B5 is inside the range B2:B100)
The solution: Change the range in cell B5 to B2:B4
Formula that causes the error when entered into cell B5:
=SUM(B2:B100)
We will change the formula so that it refers to only the cells above it, by changing the SUM range to B2:B4
Formula after fixing the error:
=SUM(B2:B4)
Note that we also could have moved the formula to cell B1, while keeping the range as B2:B100. This is why it is great to put SUM formulas at the top of your data, so that you can sum the rest of the column without the formula getting in the way.
Also note that the same error would have appeared if the formula in cell B5 referred to individual cells instead of a range, like this: =SUM(A2+A3+A4+A5)
This could be fixed with the same methods… by removing the cell A5 reference like this: =SUM(A2+A3+A4+A5)
Or the formula could be moved to cell B1 to fix the error.
The formulas depend on each other
Circular reference errors can also occur when two formulas refer to the range that the other formula resides in i.e. when two formulas refer to each other... 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.
When this happens, instead of the formula referring to a range that contains the formula… the problem is that one formula refers to a second formula, but the second formula refers back to the first formula. This means that the formulas / values are dependent on each other, which causes a circular reference error.
To fix this we change one of the formulas so there are not two formulas referring to each other. Let's go over a quick example here, and further below you will find a more detailed example of fixing this type of error.
In this example we have two formulas that are both referring to each other / are both referring to the cell that the other formula is in. The formula in cell B3 is referring to cell C3, and the formula in cell C3 is referring to cell B3. Cell B3 logic says "If cell C3 says the grade is not failing, then the grade is passing". Cell C3 logic says "If cell B3 says the grade is not passing, then the grade failing." This creates a circular reference error.
The problem: The formulas in cells B3 and C3 are referring to each other
The solution: Change the formula in cell B3, so that it refers to cell A3 instead of cell C3
Formulas that cause the error when entered into cells B3 and C3:
=IF(C3="No","Yes")
=IF(B3="No","Yes")
In this case the formula that we will change is in cell B3. We will change the formula so that is refers to cell A3 to check if the grade is passing (based on the score), and then the formula in cell C3 will remain as it is, and the circular reference errors for both formulas will be fixed, since they are not both referring to each other anymore.
Formulas after fixing the error:
=IF(A3>=60,"Yes","No")
=IF(B3="No","Yes")
What the circular reference error looks like / how to find it
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.
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.
There are several different ways that a circular reference may display an error / warning in Excel:
- 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"
- 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.
- 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
- In some cases, such as when using the FILTER function, a circular reference can cause an "Empty Array" warning / error
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:
"There are one or more circular references where a formula refers to its own cell either directly or indirectly."
"Careful, we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly."
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.
How to fix the circular reference error
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.
The problem: Cell A1 contains a formula that refers to cell A1 (refers to itself)
The solution: 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).
The problem: The SUM formula in cell A6 refers to the range A1:A10, which contains cell A6.
The solution: 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)
The problem: The FILTER formula in cell D2 refers to the range A2:D10, which contains cell D2
The solution: 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 (when two formulas depend on each other)
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 problem: The formula in cell B3 refers to cell C3, but the formula in cell C3 refers to cell B3 (the formulas refer to each other).
The solution: Change the formula in cell B3 so that it refers to cell A3 instead of cell C3.
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 (forgot tab name)
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 problem: The formula in cell A2 refers to the range A2:B14 (which contains cell A2), when the intended range was meant to be 'Another Tab'!A2:B14
The solution: Add the tab name to the reference, like this: 'Another Tab'!A2:B14
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!
