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.

**Table of Contents**hide

## 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**.

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

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

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.

### 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.

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

## 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**.

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

### 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.

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

**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.

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**

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

### 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**

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

## 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**.

It will open the **Excel Options **window. Now,

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

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.

**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.