What Is a Circular Reference in Excel?

In Excel, occurring a Circular Reference means a formula cell directly or indirectly refers to itself. Excel cannot calculate automatically when a worksheet has a circular reference. If you leave the circular reference in, Excel calculates each cell included in the circular reference using the previous iteration’s values. This creates an infinite loop that drastically slows down your spreadsheet.

Now, we will discuss more about what is Circular Reference in Excel and how you can deal with it.


Download Practice Workbook


Types of Circular Reference

If the cell in which you are inserting the formula is mentioned in the reference in any way, it will be called a circular reference. There are mainly two types of circular reference which are direct and indirect. If you insert the cell in which you are typing the formula in the reference of the formula, it is a direct circular reference. If you insert a cell in the reference of another cell, where the first cell has the second cell as reference, it is an indirect circular reference.


1. Direct Circular Reference

Consider the following dataset where we will find the total sales by adding up the cells D5 to D11 in cell D12.

dataset

Now, if we accidentally add up cell D12 with those cells in cell D12 it will cause a direct circular reference.

what is circular reference in Excel

If we press ENTER a dialog box with a circular reference error message will appear.

what is circular reference in Excel

If we click on OK, we can see the formula is showing 0 rather than the summation. This is happening because of the direct circular reference.

what is circular reference in Excel


2. Indirect Circular Reference

Look at the following example where the formula in cell D6 has reference to cell C4 and the formula in cell B8 has reference to cell D6. Now, if the formula in cell C4 has reference to cell B8, it will cause a circular referencing. Here the formula in cell C4 doesn’t refer to itself directly but if we trace the precedents we can see at some point C4 is used as a reference.

what is circular reference in Excel

If we press ENTER, cell C4 will show 0 and how the cells are related will be shown by blue arrows.

what is circular reference in Excel


Finding and Correcting Circular Reference in Excel

To get your calculation correctly, finding out the circular references and fixing them are important. There are several ways to find out the circular reference from your worksheet and now we will discuss them.


1. Show Formula Option to find Circular Reference

You can manually find out the circular references of your datasheet by displaying the formula.

➤ Go to Formulas > Formula Auditing > Show Formulas.

formula check

It will display all the formulas of your worksheet. Now you can easily identify the circular references by evaluating the formula manually.

formula


2. By Error Checking Options

You can also identify the circular reference of your worksheet by Error Checking options.

➤ Go to Formulas > Formula Auditing > Error Checking > Circular References.

Here you will see all the cells which have circular references.

error checking

Now you can take the necessary steps to fix the calculation such as correcting the reference in the formula.

error correction

Read More: How to Fix Circular Reference Error in Excel (A Detailed Guideline)


3. From Status Bar

The easiest way to find out the circular reference is to look into the status bar. If your worksheet has any circular reference, it will be shown in the status bar.

what is circular reference in Excel

Once you have found the cell which contains circular reference you can now easily take necessary action to deal with the circular reference.


Trace Relationship in Circular Reference

There are two tracing methods that will allow you to trace relationships between formulas and cells of circular references. The tracing methods are trace precedents and trace dependents. These tracing tools can assist the user in resolving circular references by displaying a path connecting the references via a line created between cells responding to the circular references.


1. Trace Precedents

The trace precedents feature keeps track of the cells that the current cell depends on.  These are the cells that the current formula relies on for the data it requires. This option will generate lines to show which cells are influencing the active cell.

To trace the precedents. First,

➤ Select the cell with circular reference.

➤ Go to Formulas > Formula Auditing > Trace Precedents or press ALT+T+U+T

precenents

As a result, you will see, a blue arrow will appear from the precedent of the active cell to the active cell.

precedents


2. Trace Dependents

The trace dependents function keeps track of the cells that are dependent on the active cell, meaning they rely on the active cell for the data they require to create outcomes. This feature will draw lines to the cells that are dependent on the active cell.

To trace the dependents. First,

➤ Select the cell with circular reference.

➤ Go to Formulas > Formula Auditing > Trace Dependents or press ALT+T+U+D

dependents

As a result, you will see, a blue arrow will appear from the active cell to the dependent cell.

dependents


Enabling Iterative Calculation to Allow Circular References

The iterative calculation is an interesting feature in Excel. When the iterative calculation is disabled, Excel returns a Circular Reference prompt and returns a 0 in the cell instead of the actual result. Now, if you want to allow circular references in your worksheet, you can enable the iterative calculation and it will allow you to perform your calculations. You can also set the number of iterations allowed. Hence, you can stop the circular reference infinite loops.

To turn on the iterative calculation,

➤ Go to the File tab and select Options.

excel options

It will open the Excel Options window. Now,

➤ Go to the Formulas tab, check the Enable iterative calculation box, and click on OK.

iterative calculation

It will enable the iterative calculation and now you can use circular reference.

If you go back to your worksheet, this time you will see the formula cell is showing a value instead of 0 and there is no circular reference error. That means now Excel is allowing circular references.

what is circular reference in Excel

Read More: How to Allow Circular Reference in Excel and Its Usage


Why Should Iterative Calculation Remain Disable?

Enabling Iterative calculation in Excel is not recommended unless it is required. When Iterative calculation is active, Excel consumes a lot of power to operate and takes much more time to make a simple calculation. Besides that, miscalculations from circular references can occur and remain unnoticed, if Iterative calculation is turned on. So we should keep iterative calculation inactive unless it is needed.


Conclusion

I hope now you know what a circular reference is in Excel and How you can deal with it. If you have any confusion, please leave a comment.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo