What Is Circular Reference in Excel?

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.

1. Direct Circular Reference
2. 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.