Often people use formulas associated with functions to make dynamic worksheets. Sometimes it might happen that reference cells depend on the result. Therefore an error named circular reference may occur in Excel. Having the knowledge to manage these errors you will be able to create more accurate formulas of your own. In this article, we will learn what is a circular reference in Excel, and how can we find and fix it.

## 📁 Download Excel File

To practice please download the Excel file from here:

## What is a Circular Reference in Excel?

**Circular Reference** is situated in an Excel cell that contains a formula that refers directly or indirectly to its own cell or the range of cells where one or multiple cells are dependent on the result. Generally, circular reference visits its range including its cells more than once and creates an infinite loop. However, it results in a **zero** value showing an **error** message.

## Circular Reference Types and Examples

If a cell contains formulas that refer to a cell that is also dependent on the result or the cell itself, circular reference will be shown. Mainly there are** two types** of circular reference.

- Direct Circular Reference
- Indirect Circular Reference

Considering the following dataset, we will learn about **Direct** and **Indirect** **Circular references**. In the dataset, there are **5 columns** as well as **11 rows**. The dataset named **Final Score of House of The Dragons** has **4 headings** named **Performer**, **Team**, **Point**, and **Game**.

**Circular Reference Types**

### 1. Direct Circular Reference

If a cell refers directly to its own or the range that contains the cell that is dependent on the result is called the **Direct Circular Reference**. Considering the dataset we mentioned above, say we need to sum up the no of **Games**. To add all the values, we use a formula including the **SUM** function in the **E12** cell. In the meantime, we mistakenly sum up the range **E6:E12** whereas we are determined to get the result at **E12**.

Press **Enter** after inserting the formula. Therefore, an **error** appears saying there are one or more circular references where a formula refers to its own cell either directly or indirectly.

Pressing **Enter** of the **Error** box, we obtain **0** as result. Here **E12** is the cell that we are determined to get the output.

**Circular Reference Types**

### 2. Indirect Circular Reference

If a cell doesn’t refer directly to its cell, but rather from the multiple cells it gets called up, is the **Indirect Circular Reference**. Considering a dataset, where some cells are dependent on one another. I.E. **A1** has a value of **2** and **B2** depends on **A1** which is the square of **A1**. Moreover, **C3** and **D4** also depend on the square of **B2** and **C3** respectively.

But for an instance, when cell **A1** gets dependent on **D4** it shows **zero**. If we explain clearly, a circular reference takes place in the **A1** cell. The Blue arrow shows the dependency of cells.

## Find and Remove Circular Reference in Excel

Trouble finding **Circular Reference**? Like yours, our main goal is to avoid **circular references** in Excel. To do so, there are **3 easy approaches** to finding the circular reference and removing them in Excel.

**Find and Remove Circular Reference**

### 1. Show Formulas Option to Find Circular Reference

One can easily navigate the formula by clicking the **Show Formula** option from the **Formulas** tab of the **Top Ribbon**. Please check out the necessary steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, select the cell i.e.
**E12**that has the issue of circular reference. - Then go to the
**Formula**tab and**drop down**the**Formula****Auditing**list. - Next, select the
**Show****formula**option.

- Therefore, we observe the formula containing the
**SUM**function with a range of**E6:E12**in the**E12**cell. - Keep in mind that we are determined to get results in
**E12**where**E12**itself is included in the formula and results in**0**because of circular reference.

- Finally, correcting the range
**E6:E11**instead of**E6:E12**in cell**E12**, we obtain the result avoiding circular reference.

**Find and Remove Circular Reference**

### 2. Error Checking Option to Find Circular Reference

One can easily find out the error cells by clicking **Error Checking** from the **dropdown** list and selecting Circular References from the **Formulas** tab of the** Top Ribbon**. Please follow the steps below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select the cell i.e.
**E12**that has the issue of circular reference. - Secondly, go to the
**Formula**tab and click on the**drop-down**icon of the**Formula Auditing**list. - Thirdly, click on the
**drop-down**icon of the**Error Checking**option and select the**Circular References**option. - Therefore, the
**$E$12**is the cell that has raised the circular reference issue.

- Now to remove circular reference write
**E6:E11**instead of**E6:E12**and get the results of Total Games.

**Find and Remove Circular Reference**

### 3. Check Circular Reference from Status Bar

By **double-clicking**, one can navigate and re-write the formula in a cell. In the meantime, the cell shows a circular reference in the **Status Bar** that is located at the bottom right of the worksheet. Please follow the process below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Primarily, select the cell i.e.
**E12**that has the issue of circular reference. - Then,
**double-click**on the**E12**cell to**re-write**the formula in the cell. - In the meantime,
**Circular Reference: E12**is shown in the**status bar**located at the bottom left.

- Accordingly, one may correct the formula by putting the range
**E6:E11**in lieu of**E6:E12**and get the final result.

## How to Fix Circular Reference Error in Excel

Are you in trouble with Circular Reference? Don’t worry. Keep faith in us. Fixing Circular Reference Errors will be your cup of tea following the below** two ways**.

**Fix Circular Reference**

### 1. Correct Cell Reference

The simple way is to change the cell reference. When you put its own cell reference, you simply get a zero which is basically an Error related to Circular Reference. To eliminate the error please check out the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Primarily, it results in
**0**when we put the cell reference of**B5**in the**B5**cell occurring as a Circular Reference.

- After that, when we change its cell reference to
**B3**in the**B5**cell. It results in the**Final Score of House of The Dragons**.

**Fix Circular Reference**

### 2. Change the Formula

By Changing the formula along with its cells and ranges, one may avoid Circular Reference. Please check out the below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, it results in
**0**when we put the formula of**SUM(E6:E12**) occurring despite a Circular Reference.

- However, when we re-write the formula as
**SUM(E6:E11)**in the cell**E12**we are simply able to avoid Circular Reference and achieve the final result.

## How to Trace Relationship in Circular Reference

From the **Formula Auditing** feature of the **Formula** tab, one can easily define the track of formula relation. Discover **two easy methods** to delineate the path of formula execution in cells.

**Trace Relationship**

### 1. Trace Precedents Option

The **Trace Precedents** option from the **Formula Auditing** feature tracks the cells and generates lines. It shows the track on which it relies. Please check out the below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select the cell i.e.
**A1**that has the issue of circular reference. - Then, go to the
**Formula**tab and**drop down**the**Formula Auditing**list. - Next, click on the
**Trace Precedents**option.

- Therefore, it shows the clear path that
**A1**is fully dependent on the**D4**.

**Trace Relationship**

### 2. Trace Dependents Option

The trace Dependence option from the Formula Auditing feature outlines the next move. It shows the track of the next move depending on the current cell. Please check out the below.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select the cell i.e.
**A1**and**C3**where**A1**has the issue of circular reference. - Then, go to the
**Formula**tab and**drop down**the**Formula Auditing**list. - Next, click on the
**Trace Dependents**option.

- Therefore, The blue arrow mark delineates the results of
**B2**depending on the**A1**; whereas**D4**depends on the**C3**cell.

## Enable Iterative Calculation to Allow Circular References in Excel

**Enabling Iterative Calculation** options from the **Excel option** can neglect Circular Reference but it doesn’t show the accurate result as it runs the infinite loops. Please follow the steps to enable the Iterative Calculation option.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, go to the
**File**option of a workbook. - Then, click on the
**Option**from**More**of the**Side Ribbon**of the workbook.

- Thus a box named
**Excel Option**appears. - Next, go to the
**Formulas**section and click on**Enable iterative calculation**from the**Calculation options**. - Further, hit the
**OK**button.

- Now click on the cell that was an issue of CIrcular Reference is showing the number though inaccurate.

## Why Should Iterative Calculation Be Disabled?

Generally, **Iterative Calculation** remains **Disabled**. However, one may enable Iterative calculation to get rid of Circular Reference. Enabling Iterative calculation slows down Computer performance as it consumes so much power and runs infinite loops until a certain number is met. Perhaps a miscalculation takes place in major cases and it gets unnoticed. In the meantime, limiting the Maximum Iteration may reduce execution time and perform better.

## 📄 Important Notes

🖊️ Make sure that the cell itself should not depend on the result, Thus Circular Reference will take Place.

🖊️ Be aware of **Indirect Circular** References. In some cases, it is difficult to find out.

🖊️ **Double-Click** on the cell is essential to observe the **Circular Reference** in the **Status** **Bar** of a Worksheet.

🖊️ **Enabling Iterative Calculation** may slow down your computer.

## 📝 Takeaway from This Article

📌 Difference between **Direct **and **Indirect Circular Reference **as well as examples.

📌 **Show Formulas **option to observe the formula that is used in the cell.

📌 **Error Checking **option to find out the non-conformity cell in the formula.

📌 Checking the Circular Reference from the **Status Bar**.

📌 Use of **Trace Precedents **and **Trace Dependence** in the dataset.

📌 Enable the **Iterative Calculation** option to get rid of **Circular Reference**.

## Conclusion

In this article, we described circular reference Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Don’t forget to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit **www.ExcelDen.com**.