How to Find and Fix Circular References in Excel

A circular reference occurs when you end up having a formula in a cell – which in itself uses the cell reference (in which it’s been entered) for the calculation. If this statement seems a bit confusing, don’t worry by the end of this tutorial it will start to make sense.

In simple terms, a circular reference happens when a formula references back to its own cell directly or indirectly, creating an endless loop of calculations. This endless reference loop, if not stopped will keep changing the cell's value every time.

Excel Circular References

For quick understanding, here’s an example.

In our example, there are a few numbers in column A and we are trying to calculate their sum using the SUM function in A7 cell. But inside the SUM function, we have passed the range as A1:A7.

Excel-Circular-References_Error_Message

 

Since the supplied range also includes A7 cell (in which the result needs to be populated) so it creates an endless loop as Excel just keeps on adding the new value in cell A7, which keeps on incrementing.

This triggers Excel's circular reference warning that says, "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.".

Keep in mind that this is not an error since it will not stop further calculations or ask the user to change them. It is a warning that warns the user that they could probably have incorrect calculations.

Direct and Indirect Circular References

Circular references can be categorized into two types –  1) Direct Circular References 2) Indirect Circular References. Let's try to understand both of these first.

Direct Circular Reference

A direct circular reference is pretty straightforward. The direct circular reference warning message shows up when the formula in a cell is referring to its own cell directly.

For example, see the data below.

Excel-Direct-Circular-References 02

  • The formula in cell B8 refers to itself. When the user applies a function such as a sum or manually uses the reference of B8, a direct circular reference warning is displayed.
  • Once the warning shows up, the user can click on OK, but it will only result in zero.

Excel-Direct-Circular-References_Output_03

This is an example of a direct circular reference in Excel.

Indirect Circular Reference

As the name suggests, an indirect circular reference takes place when a value in a formula refers to its cell, not directly but at some level. See the simple example below for a better understanding.

For creating an indirect circular reference example, we will have to write a chain of formulas using values starting from the first cell.

  • For example, the data is populated in a way where the diagonals are squares of the previous diagonals as you can see.

Excel-InDirect-Circular-References_04

  • Now the data is starting from A1 which has the value 10 and every other diagonal till D4 is dependent on A1. If the user uses a reference of any diagonal in A1, it will create a circular reference warning. Let’s try it out.
  • As seen in the screenshot below, Excel goes as far as creating a linked line showing the Precedents and Dependents.

Excel-InDirect-Circular-References_Connections_05

  • It results in a value of 0 since it is a circular reference issue.

Now, let's try to see and understand how to find circular reference issues in excel.

How to Find Circular References in Excel

While you will get the circular reference warning when it occurs, however, you will still need to figure out in which cell the error has occurred. After knowing the exact cell location of the error it will be easier to handle and fix it.

These methods are particularly helpful when you are working with large datasets.

1) Error Checking drop-down in the Ribbon

Here’s how you can find circular references in Excel using the Ribbon.

  • Open the worksheet where the circular reference has occurred.
  • Go to the Formulas tab and click on the Error Checking drop-down menu.

Error_Checking_Circular-References_07

  • Select Circular References from the drop-down menu.
  • Here Excel will show you all the circular references that are in the worksheet.
  • Click on whichever circular reference you want and it will take you to that particular cell to solve the issue.

As you can see in the screenshot above, there are 4 circular references in the worksheet that we are using.

2) Using Status bar

Finding circular references using the status bar is very easy. If a circular reference exists in the worksheet, the user will be able to see it in the status bar below the worksheet names.

Circular-References_In_StatusBar_08

Note: It only shows the latest circular reference and the user can backtrack from there. As seen in the example below, there are two circular references notably in B8 and C8 but the status bar only shows C8.

Iterative Calculations

Iterative calculations are an interesting feature in Excel. What iterative calculations mean is that if the user keeps them disabled as they usually are, Excel returns a Circular Reference prompt and returns a 0 in the cell instead of the actual result. This is because it is an endless loop.

Now, the thing is that if you want to tell Excel to sit out and let you do your job without bothering you about those pesky circular reference warnings, you can enable Iterative Calculations and it will allow you to perform your calculations. Not only that, but it will also allow you to set a fixed number of maximum iterations.

If you wish to enable iterative calculations, you can set the number of iterations allowed. Hence, you can stop the circular reference infinite loops and can have a tiny bit of control over such calculations.

How to Enable/Disable Iterative Calculations in Excel

Now that we have established how iterative calculations work, let’s show you how you can enable or disable iterative calculations in Excel. Follow the below steps to enable or disable iterative calculations in excel.

  • Go to the File tab.
  • Click on Options.
  • The Excel Options dialog box will open. Click on Formulas from the left column.

Enable_Iterative_Calculations_09

  • Here, the option to Enable Iterative Calculation is available. Users can simply tick the box to enable. The user is also able to set maximum iterations and maximum change.
  • After enabling iterative calculation, you will see that the calculation will not result in 0 but will produce results in case of circular reference as you can see.

Circular-References_With_Iterative_Calculations_Enabled_10

Maximum Iterations & Maximum Change Parameters

The two parameters of the Iterative Calculations are:

Maximum iterations: This is the loop that Excel will run when it is calculating the final result. You can set this as you want. Remember that more iterations mean more processing for Excel which will in turn use more resources and processing power from the computer. It would also take more time.

Maximum change: The maximum change is the value that needs to be achieved in order for the iteration to move further. It is about the accuracy of the result. Make this value small as it will produce accurate results. The default Maximum Change is 0.001.

Deliberately Using Circular References

The deliberate use of Circular references is not a very good idea, though sometimes it can help you to get away with poorly developed logic or formulas. But doing this is not recommended at all. However, for educational purposes let's try and understand how to get things working with deliberate circular references.

The first step is to enable Iterative Calculation in Excel. Once you have turned on Iterative Calculation and set your maximum iterations, you can start using circular references to your benefit.

Let us demonstrate that by using an example.

Let’s say that the user requires to manipulate the data in a way that two cells depend on each other for values causing a circular reference issue. Since the iterative calculation is enabled, there won’t be a prompt.

In the example below, the cells B4 and B5 depend on each other, creating a circular reference. Since the iterative calculation is enabled, there will not be an error or a 0 in the results. Instead, Excel will try calculating the results.

In the example, the Agent fee is 20% of the total earnings of the player. Since the total earning and agent fee are dependent on each other, it can still be calculated if iterative calculation is enabled.

Deliberate-use-of-Circular-References_11

To use it as we see fit, we must first apply our desired formula in the net earnings cell.

Deliberate-use-of-Circular-References_12

Now the Agent fee must be calculated which we are going to set at 20% of total earnings but the total earnings will then have to subtract the Agent fee from itself, hence the circular reference.

Deliberate-use-of-Circular-References_11

As you can see, it works perfectly.

Why Should We Avoid Using Circular References Deliberately?

Using Circular References in Excel is not recommended at all. Deliberate use of circular references is a slippery method to make things work that eats up a lot of resources and processing power when being used with iterative calculations. It can often produce undesired or inaccurate results that can cause frustration when you have to fix a few of them.

How to fix Circular References in Excel

Fixing circular references in Excel is not possible with a single click. To handle circular references, you have to eliminate them one by one. You can trace it back to the source and remove the starting formula to fix it, or you can remove it one by one.

There are two tracing methods that will allow you to remove circular references by tracing relationships between formulas and cells.

To access the tracing methods, you have to follow the following steps:

  • Go to the Formulas tab in Excel
  • The Trace Precedents and Trace Dependents options are available there.

Trace Precedents_And_Trace Dependents_13

These tracing features can help the user in fixing circular references by providing a path connecting the references through a line that is drawn between cells that are responding to the circular references.

The two tracing options work as follows:

Trace Precedents

The trace precedents feature tracks back cells that the current cell depends on. These are the cells on which the current formula is dependent for the data that it needs. This option will draw lines that will tell which cells are affecting the active cell.

In the example below, the cells affecting B5 are B2, B3, and B4. Hence, when we click on Trace Precedents, it draws a line indicating B2, B3, and B5 cells leading to B5.

Trace Precedents_lines_14

Shortcut for Trace Precedents: ALT + T U T

Trace Dependents

The trace dependents feature tracks the cells that are dependent on the active cell, the cells that depend on the current cell for the data they need to produce results. This feature will draw lines to the cells that are dependent on the active cell.

In the example below, the cell being affected by B5 is B4. It is dependent on B5 for its value. Hence, when we use trace dependents, it draws a line from B5 to B4, indicating that B4 is dependent on B5.

Trace Dependents_Lines_15

Shortcut for Trace Dependents: ALT + T U D

So, this was all about circular references in Excel. Hopefully, by now, you have a good idea of how circular references work, how you can find/fix them, and how you can use them if you desire.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.