If we use Excel for any length of time to create the dashboards, templates, reporting format, etc. in which we have to use formulas then the possibility is to create a formula that results in a circular reference. In this article, you will learn about Circular reference in Microsoft Excel. What is the use of Circular reference?
When the formula depends on the same cell in which it is written, Excel pops up an error message. In other words, you should not write a formula in a cell that depends on itself. It doesn't mean we can't.
For example, if you try to put values 10 in Cell A1 only if A1 is blank, you will write a formula in cell A1 and it will check A1. The formula will be something, IF(ISBLANK(A1),10, A1). It won't work unless and until we unable the circular referencing. It will also result in circular referencing when the formula depends on another formula and that formula depends somehow on the first cell itself. Confusing? Have an example?
Let’s take another example and understand:-
In the above-shown image, we have agent data. Below the data, we have a summary for a number of agents and average scored value. To calculate the number of agents we have used COUNTA function and for average we have to use the Average function.
Now to know about the circular reference follow the steps below:-
Now to check the reason of showing the circular reference follow the steps below:-
Conclusion: - When you are working in Excel sheet for templates and dashboard this problem is very common so it occurs. This article will be helpful to rectify this kind of problem while working in Excel.
On the Excel ribbon, click on the File button. Go to Options. Excel Options Dialog will open.
Click on the Formula option. Here you will find Allow iterative calculations. Check to allow circular calculations. You can control the number of iterations and maximum change too.
This will allow the circular referencing, and excel will not show any error when do circular referencing but you might not get the answer you wanted.
So yeah guys, this what circular referencing in Excel. I explained why a circular referencing error occurs and how you can allow circular referencing. I hope it was helpful to you. If you have any doubts regarding this topic, ask in the comments section below.
Related Articles:
What is an Absolute Reference in Excel | In many of my articles, you must have read word absolute reference. If not, you must have notices $ (dollar) sign before cell references ($A$1). They are absolute references. When we prefix the $ sign before
Expanding References in Excel | The ranges that expand when copied in the below cells or to right cells are called expanding references. Where do we need expanding references? Well, there were many scenarios
Relative and Absolute Reference in Excel | Understanding of Relative and Absolute Reference in Excel is very important to work effectively on Excel. Relative and Absolute referencing of cells and ranges
Dynamic Worksheet Reference | Dynamic worksheet reference suggests that using a value in one sheet from another. The INDIRECT function is a cell reference function that allows us to make the text reference into actual reference.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
