In this article, we will explore the concept of Circular Reference in Excel. We’ll understand why it’s important to avoid them and learn how to find them in your worksheet.
We’ll also learn methods to fix or break the circular reference loop. Along the way, we’ll provide examples to help you see how circular references work in practical situations.
By the end of this article, you’ll have a clear understanding of circular references and be able to handle them effectively in your Excel spreadsheets.
Circular references in Excel are like tricky puzzles. They occur when a formula refers back to its own cell or creates a loop of references. These can mess up your calculations and slow down your computer.
Here, we will help you understand and handle circular references effectively. Get ready to solve the puzzle and master circular references in Excel for accurate calculations and smoother performance.
⏷What is Excel Circular Reference
⏵Direct Circular Reference
⏵Indirect Circular Reference
⏷When Does a Circular Reference Appear
⏷Why should You Avoid Circular Reference
⏷Find Circular Reference
⏵Use Status Bar
⏵Activate Error Checking
⏷Remove Circular Reference
⏵Check Cell References of Formula
⏵Check Indirect Reference
⏵Copy Formula to Difference Cell
⏷Enable Circular Reference
⏷Example of Circular Reference
⏷Use Trace Precedents/Dependents
⏷Frequently Asked Questions
⏷Excel Circular Reference: Knowledge Hub
What is an Excel Circular Reference
In Excel, a circular reference is a formula that refers back to its own cell either directly or indirectly.
It creates a situation where Excel cannot determine the value of the cell because it depends on itself. This leads to an error and can cause incorrect calculations or an infinite loop.
Suppose you have a spreadsheet where cell A1 is supposed to contain the sum of A1 and B1. In other words, the formula in A1 is:
=A1+B1This creates a circular reference because the formula is referencing itself directly. Excel will show this as an error.
1. Direct Circular Reference
A direct circular reference occurs in Excel when a formula in a cell refers directly back to the same cell. In other words, the formula is self-referential and creates a loop within that cell.
For example, in D12, we have the following formula
=SUM(D5:D12)
The result is zero because a direct circular reference error has occurred here. We have taken D12 as an element of the SUM function and used the formula in D12.
2. Indirect Circular Reference
An indirect circular reference occurs when multiple cells are dependent on each other. This eventually leads to a cell being dependent on its own value, creating a loop. This happens due to wrong referencing most of the time.
This is a group of cells with simple relationships among them.
Now, if we try to write down the following formula in C4, we will get an indirect circular reference.
=B8*7
Explanation:
- Here, D6 depends on C4 & B8 depends on D6. So, if we try to make C4 depending on B8, B8 becomes dependent on its own value indirectly.
That’s why an indirect circular reference occurs.
When Does a Circular Reference Appear?
A circular reference error happens in Excel when you do the following:
- Create a loop where a formula refers to its own cell.
- Create a new loop after removing any previous ones.
- Open a workbook with a loop that hasn’t been resolved.
When this error occurs, you’ll see a message box in the middle of your screen like the following image. The message alerts you by showing that there is a circular reference problem in the workbook, and this might lead to incorrect calculations.
Click Here to See the Full Image
To help you identify the issue, blue arrows appear on the spreadsheet, showing the incorrect calculation chain.
- Excel doesn’t keep showing notification messages repeatedly when we use multiple formulas with circular references.
- When we open a saved file in Excel, we might see a warning message saying that there is a circular reference in one or more formulas.
Why Should You Avoid Circular Reference?
Circular references in Excel cause several problems. Apart from making your workbook slower and showing a warning message, every time you open it (unless you enable a specific setting), circular references can lead to other unexpected issues.
For example, if you accidentally click on a cell that has a circular reference and then edit the cell, the result will be zero.
How to Find Circular Reference in Excel
1. Using Status Bar
The most convenient method to find a circular reference is to check the Status Bar. If there is a circular reference, Excel will show it in the Status Bar. You will find the Status Bar at the bottom of the sheet.
2. Activating Error Checking
You can also activate the Error Checking feature to find a circular reference.
- Go to the Formulas tab >> select Formula Auditing >> select the drop-down beside Error Checking >> get the cell address where a circular reference exists.
Read More: How to Find a Circular Reference in Excel
How to Remove Excel Circular Reference?
1. Check Cell References of Formula
The first method is to check the formula. For example, the initial formula was,
=SUM(D5:D12)But you have to change it the following way.
=SUM(D5:D11)
2. Check for Indirect References
If the direct reference is ok, then you should look for any error in the indirect reference and fix the error.
In the following example, C4 and D6 are related and dependent on each other.
Since the referencing is not correct, you should fix it and change the referencing. The new formula in D6 is below.
=SUM(B5+10)
3. Copy Formula to Different Cell
Another method to deal with a circular reference is to copy the formula in a different cell. For example, the formula was initially in D12, resulting in an error. But when I have incorporated it in D13, it works fine.
=SUM(D5:D12)
Read More: How to Remove Circular Reference in Excel
How to Enable Circular Reference in Excel
Circular references can be enabled in Microsoft Excel by activating iterative calculations.
- Go to the File tab >> select Option >> Formulas.
- Excel Options window will appear. Tick the Enable iterative calculation box.
- Set Maximum Iteration to 1 and Maximum Change to 001 >> click OK.
Now, you will see that Excel will get you the correct result.
Maximum Iterations: This determines how many times Excel will keep calculating until it reaches a final result. You can choose the number of iterations you want, but keep in mind that higher numbers may take more time and computer resources.
Maximum Change: This setting defines the level of accuracy for the calculations. It represents the maximum allowable difference between each iteration. A smaller maximum change value means more precise results. By default, Excel uses a value of 0.001 for maximum change.
Read More: How to Allow Circular Reference in Excel
Example of Excel Circular Reference
Sometimes, you can use a circular reference in Excel. I am showing you a potential example of the use of a circular reference in this section.
Suppose we have a list of Product(s), their Delivery status, and Time of delivery. You can find the Time of delivery using the NOW function. Insert the following formula on cell D5.
=IF(C5="Yes",IF(D5="",NOW(),D5),"")
Explanation: In the formula mentioned, there are two parts.
- IF(C5=”Yes”,IF(D5=””,NOW(),D5),””)
The first part checks if cell C5 contains a specific text (like “Yes” or any text you choose). If it does, the formula moves on to the second part. If not, it leaves the result empty.
- =IF(C5=”Yes”,IF(D5=””,NOW(),D5),””)
The second part looks at cell D5 and does something only if it doesn’t already have a value in it. In this case, it fills in the time by using the NOW function. This helps keep any existing time information in D5 while adding the current time if D5 is empty.
Even though the formula works fine here, you should always avoid a circular reference and find an alternate way. In this case, we can use the following alternative formula.
=IF(C5="Yes",NOW(),"")
This formula avoids a circular reference.
How to Use Trace Precedents/Dependents to Check Circular Reference
Excel’s Trace Precedents and Trace Dependents feature can give you indications about the relationship of the formulas with the cell. Trace Precedents draws arrow to show the cell or cell range which are used in the formula. On the other hand, Trace Dependents shows the relationship of the selected cell with the formula by drawing arrow.
You can use this feature to see if you have used any circular reference or not. Suppose I have accidentally selected a wrong range of data inside the SUM formula. Now, if I want to see the range of cells I have selected in the formula, I can use Trace Precedents. Follow the steps:
- Go to the Formulas Tab >> select the cell D12 which contains the formula >> click on the Trace Precedents option under Formula Auditing group of commands.
Therefore, you see an arrow is drawn showing the range of cells. Now, I can easily see that, I have accidentally included the cell D12. This cell contains the formula itself. So, it will give a circular reference.
- To remove arrows, click on the Remove Arrows option.
If you want to see the relation of a cell with the formula, you can use the Trace Dependents option.
- Go to the Formulas Tab >> select the cell D7 which is used in a formula >> click on the Trace Dependents option under Formula Auditing group of commands.
As a result, an arrow has been drawn to show the relation of the D7 cell with the formula where D7 cell is used.
Frequently Asked Questions
1. Why should I use a circular reference in Excel?
Answer: Circular references are typically used when you need the formula to update continuously based on its own result. It can be helpful for iterative calculations or when you want to create dynamic models.
2. Are circular references always bad in Excel?
Answer: Circular references are not always bad, but they need to be used with caution. In some cases, they can solve specific problems or create dynamic models. However, if not managed properly, circular references can lead to incorrect results or infinite loops.
3. How can I troubleshoot circular references in Excel?
Answer: Excel provides tools to help troubleshoot circular references. You can use the “Circular References” option in the “Formulas” tab to locate and analyze circular references in your worksheet. It also provides suggestions to resolve them.
Download Practice Workbook
You can download this practice workbook while going through the article.
Conclusion
In this article, we have explored the nature of Excel circular reference, understood why they should generally be avoided, and learned how to identify and manage them effectively. Understanding circular references in Excel is crucial for getting accurate results and keeping your spreadsheets running smoothly.
Remember to be careful and use the techniques we discussed to break circular loops or enable iterative calculation when necessary. With these skills, you can confidently solve any circular reference issues and make the most of Excel’s data analysis features.
Excel Circular Reference: Knowledge Hub
- Excel Iterative Calculation Not Working
- Enable Iterative Calculation
- Fix Circular Reference Error
- Fix Circular Reference That Cannot be Listed
<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel
