Excel Circular Reference (Table of Contents)
- Circular Reference in Excel
- How to Use Circular Reference in Excel?
- Find Circular Reference in Excel
Circular Reference in Excel
Many times while working with formulas in Excel, it displays an error message. Sometimes it can be related to Circular Reference. This problem occurs when we include the same cell in the formula calculation where the formula exists, means including the formula cell too, then Excel displays an error message.
What is a circular reference in Excel?
While applying the formula in a cell, if it contains directly or indirectly its own cell (where the formula resides) then it’s called an Excel Circular Reference.
Mostly we should avoid this condition in Excel but sometimes it happens when we require to create the Circular reference.
How to Use Circular Reference in Excel?
Using It is very simple and easy. Let’s understand the situation of Circular Reference error with some examples.
Example #1
- Now as we can see that Cell A4 and A5 has some value and in Cell A6, we have to add those values including the value in Cell A6 itself.
- So we enter the formula like the below screenshot.
- When we hit the enter key, it will display an error message as shown below.
- Here if you click on OK or close the window by clicking on the cross button, Excel will take it as Zero value or the last calculated value in the cell.
- If you click on Help, you can get more information.
- Now, we have clicked on OK, and it will display 0 in the cell A6. ( Refer below screenshot )
You might be surprised that in some cases, excel will complete formula with the last calculation having Circular reference condition without displaying an error message and returns the value in the cell.
In some conditions, we might require to create a circular reference while calculating. Let’s take an example to create that scenario where circular reference is required to include.
Example #2
Suppose you go to a restaurant and have lunch. You get the bill which you have to pay before leaving. The GST ( Goods and Service Tax ) on total expense is 10%, so we have to calculate the total bill.
Here, we will apply the formula like below screenshot.
Here we will sum up all the Item prices and calculate the 10% on that amount. Then will add this tax amount in total items price. It will give you the total expenses.
But here Circular reference is a part of the formula, it won’t calculate the total bill. In this situation, we need to make some changes in Excel settings for further calculation.
In this scenario, it will display an error message as shown in the above Example 1 ( Figure 3 ).
Find Circular Reference in Excel
Follow the below steps :
- Go to the FILE tab.
- Click on Options in the left pane window. Refer below screenshot.
- It will open a dialog box for Excel options.
- Click on Formulas tab.
- Tick on checkbox “Enable iterative calculation” as shown in below screenshot.
- Click on OK.
- It will calculate the Total Bill in Cell B15. Refer below screenshot.
By enabling this Iterative calculation option, it will repeat the re-calculation until a specific numeric condition is met.
There are two ways to turn on this option.
We can see there are two options available under the Enable iterative calculation section as shown in below screenshot.
- Maximum Iterations: As we can see here, the default value is 100. Which means Excel will repeat the calculations 100 times and will try to solve this.
- Maximum Change: Excel will try to repeat the calculation 100 times and stops when it finds the difference is not more than 0.001 between iterations.
Things to Remember
- We should avoid Circular reference, the reason being, it leaves an impact on the performance and displayed a warning message every time at the time of opening workbook.
- It can also be the reason for other issues which are not immediately visible.
Recommended Articles
This has been a guide to Circular Reference in Excel. Here we discuss how to find and use a circular reference in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
