How to Find Circular References in Excel

One of the most common error warnings users encounter in Excel is the ‘Circular Reference’. Thousands of users have the same problem, and it occurs when a formula references back to its own cell directly or indirectly, causing an endless loop of calculations.

For example, you have two values in cells B1 and B2. When the formula =B1+B2 is entered into B2, it creates a circular reference; the formula in B2 repeatedly recalculates itself because each time it calculates, the B2 value has changed.

Most circular references are unintended mistakes; Excel will warn you about these. However, there are also intended circular references, which are used to make iterative calculations. Unintended circular references in your worksheet might cause your formula to calculate incorrectly.

Therefore, in this article, we will explain all that you need to know about circular references, and well as how to find, fix, remove, and use circular references in Excel.

How to Find and Handle Circular Reference in Excel

When working with Excel, we sometimes encounter circular reference errors which happen when you enter a formula that includes the cell where your formula resides. Basically, it happens when your formula is trying to calculate itself.

For example, you have a column of numbers in cell A1:A4 and you are using the SUM function (=SUM(A1:A5)) in cell A5. The cell A5 directly refers to its very own cell, which is wrong. Hence, you will get the following circular reference warning:

Once you got the above warning message, you can click the ‘Help’ button to know more about the error, or close the error message window by clicking either the ‘OK’ or ‘X’ button and get ‘0’ as the result.

Sometimes circular reference loops may cause your calculation to crash or slow down your worksheet performance. Circular reference can also lead to a number of other issues, which won’t be evident immediately. So, it is best to avoid these.

Direct and Indirect Circular References

The Circular references can be categorized into two types: Direct Circular References and Indirect Circular References.

Direct Reference

A direct circular reference is pretty simple. The direct circular reference warning message pops up when the formula is referring back to its own cell directly.

In the below example, the formula in cell A2 directly refers to its own cell (A2).

Once the warning message pops up, you can click on ‘OK’, but it will only result in ‘0’.

Indirect Circular Reference

An indirect circular reference in Excel occurs when a value in a formula refers back to its own cell, but not directly. In other words, circular reference can be formed by two cells referencing each other.

Let’s us explain with this simple example.

Now the value is starting from A1 which has the value 20.

Next, cell C3 refers to cell A1.

Then, cell A5 refers to cell C3.

Now replace the value 20 in cell A1 with the formula as shown below. Every other cell is dependent on cell A1. When you use a reference of any other previous formula cell in A1, it will cause a circular reference warning. Because, the formula in A1 refers to cell A5, which refers to C3, and cell C3 refers back to A1, hence the circular reference.

When you click ‘OK’, it results in a value of 0 in cell A1 and Excel creates a linked line showing the Trace Precedents and Trace Dependents as shown below. We can use this feature to easily find and fix/remove circular references.

How to Enable / Disable Circular References in Excel

By default, iterative calculations are turned off (disabled) in Excel. Iterative calculations are repetitive calculations until it meets a specific condition. When it is disabled, Excel shows a Circular Reference message and returns a 0 as the result.

However, sometimes circular references are needed to calculate a loop. To use circular reference, you must enable iterative calculations in your Excel and it will allow you to perform your calculations. Now, let us show you how you can enable or disable iterative calculations.

In Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Microsoft 365, go to the ‘File’ tab in the upper left corner of Excel, then click ‘Options’ in the left pane.

On the Excel Options window, go to the ‘Formula’ tab and tick the ‘Enable iterative calculation’ check box under the ‘Calculation options’ section. Then click ‘OK’ to save the changes.

This will enable iterative calculation and thus allowing circular reference.

To achieve this in pervious versions of Excel follow these steps:

In Excel 2007, click on the Office button > Excel options > Formulas > Iteration area.
In Excel 2003 and earlier versions, you need to go to Menu > Tools > Options > Calculation tab.

Maximum Iterations & Maximum Change Parameters

Once you enable iterative calculations, you can control the iterative calculations, by specifying two options available under the Enable iterative calculation section as shown in the screenshot below.

Maximum Iterations – This number specifies how many times the formula should recalculate before giving you the final result. The default value is 100. If you change it to ’50’, Excel will repeat the calculations 50 times before giving you the final result. Remember that the higher the number of iterations, the more resources and time it takes to calculate.
Maximum Change – It determines the maximum change between calculation results. This value determines the accuracy of the result. The smaller the number, the more accurate result the result would be and the longer it takes to calculate the worksheet.

If the iterative calculations option is enabled, you won’t get any warning whenever there’s a circular reference in your worksheet. Only enable interactive calculation when it’s absolutely necessary.

Find Circular Reference in Excel

Suppose you have a large dataset and you got the circular reference warning, you will still need to find out where (in which cell) the error has occurred in order to fix it. To find circular references in Excel, follow these steps:

Using Error Checking Tool

First, open the worksheet where the circular reference has happened. Go to the ‘Formula’ tab, click on the arrow next to the ‘Error Checking’ tool. Then just hover the cursor over the ‘Circular References’ option, Excel will show you the list of all cells that are involved in the circular reference as shown below.

Click on whichever cell address you want in the list and it will take you to that cell address to solve the issue.

Using Status Bar

You can also find the circular reference at the Status bar. On Excel’s status bar, it will show you the latest cell address with a circular reference, such as ‘Circular References: B6’ (see below screenshot).

There are certain things you should know when handling circular reference:

The status bar will not show the circular reference cell address when the Iterative Calculation option is enabled, so you need to disable it before you start looking at the workbook for circular references.
In case circular reference is not found in the active sheet, the status bar only displays ‘Circular References’ with no cell address.
You will only get a circular reference prompt once and after you click ‘OK’, it will not show the prompt again the next time.
If your workbook has circular references, it will show you the prompt every time you open it until you resolve the circular reference or until you turn on iterative calculation.

Remove a Circular Reference in Excel

Finding Circular references is easy but fixing it is not that simple. Unfortunately, there’s no option in Excel that will let you remove all circular references at once.

To fix circular references, you have to find each circular reference individually and try to modify it, remove the circular formula altogether, or replace it with another one.

Sometimes, in simple formulas, all you need to do is to readjust the parameters of the formula so that it does not refer back to itself. For example, change the formula in B6 to =SUM(B1:B5)*A5 (changing B6 to B5).

It will return the result of the calculation as ‘756’.

In cases when an Excel circular reference is hard to find, you can use the Trace Precedents and Trace Dependents features to trace it back to the source and solve it one by one. The arrow shows which cells are affected by the active cell.

There are two tracing methods that can help you to delete circular references by showing the relationships between formulas and cells.

To access the tracing methods, go to the ‘Formulas’ tab, then click either ‘Trace Precedents’ or ‘Trace Dependents’ in the Formula Auditing group.

Trace Precedents

When you select this option, it tracks back the cells that affect the active cell’s value. It draws a blue line indicating which cells affect the current cell. The shortcut key to use trace precedents is Alt + T U T.

In the example below, the blue arrow shows the cells that affect the B6 value are B1:B6 and A5. As you can see below, cell B6 is also part of the formula, which makes it a circular reference and causes the formula to return ‘0’ as the result.

This can be easily fixed by replacing B6 with B5 in SUM’s argument: =SUM(B1:B5).

Trace Dependents

The trace dependents feature traces the cells that are dependent on the selected cell. This feature draws a blue line indicating which cells are affected by the selected cell. That is to say, it displays which cells contain formulas that reference the active cell. The shortcut key to use dependents is Alt + T U D.

In the following example, cell D3 is affected by B4. It is dependent on B4 for its value to produce results. Hence, trace dependent draws a blue line from B4 to D3, indicating that D3 is dependent on B4.

Deliberately Using Circular References in Excel

Using Circular references deliberately is not recommended, but there might be some rare cases where you need a circular reference so that you can get the output you want.

Let us explain that by using an example.

To start with, enable ‘Iterative Calculation’ in your Excel workbook. Once you have enabled Iterative Calculation, you can start using circular references to your advantage.

Let’s assume you’re buying a house and you want to give a 2% commission on the total cost of the house to your agent. The total cost will be calculated in cell B6 and the commission percentage (agent fee) is calculated in B4. The commission is calculated from the total cost and the total cost includes the commission. Since the cells B4 and B6 depend on each other, it creates a circular reference.

Enter the formula to calculate the total cost in cell B6:

=SUM(B1:B4)

Since the total cost includes the agent fee, we included B4 in the above formula.

To calculate Agent Fee of 2%, insert this formula in B4:

=B6*2%

Now the formula in cell B4 depends on the value of B6 to calculate 2% of the total fee and the formula in B6 depends on B4 to calculate the total cost (including agent fee), hence the circular reference.

If the iterative calculation is enabled, Excel won’t give you a warning or a 0 in the result. Instead, the result of cells B6 and B4 will be calculated as shown above.

The iterative calculations option is usually disabled by default. If you didn’t turn it on and when you enter the formula in B4 which will create a circular reference. Excel will issue the warning and when you click ‘OK’, the tracer arrow will be shown.