Yep, in some cases, a formula with a circular reference can complete successfully before it tries to calculate itself, and when that happens, Microsoft Excel returns the value from the last successful calculation. When you close the message window, Excel displays either a zero (0) or the last calculated value in the cell. Once you got the above warning, you can click Help for more information, or close the message window by clicking either OK or the cross button. Why does Microsoft Excel give you a heads-up? Because Excel circular references can iterate indefinitely creating an endless loop, thus significantly slowing down the workbook calculations. =A1*5 or =IF(A1=1, "OK").Īs soon as you hit Enter to complete such a formula, you'll get the following warning message: Entering any other formula or calculation referring to A1 would have the same affect, e.g. " When an Excel formula refers back to its own cell, either directly or indirectly, it creates a circular reference."įor instance, if you select cell A1 and type =A1 in it, this would create an Excel circular reference. Here is a very straight and concise definition of a circular reference provided by Microsoft:
Are you sure you want me to proceed anyway?"Īs you can understand, circular references in Excel are troublesome, and the common sense says to avoid them whenever possible. To put it simply, what Excel is trying to say is this: "Hey, I may get stuck at the round-about. "Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly." When you try to do this, Excel throws up the following error message:
Thousands of users are facing the same problem on a daily basis simply because of forcing an Excel formula to calculate its own cell. Is this how you ended up on this page? :) Instead, it tells you something about a circular reference. You've tried to enter some formula in your Excel sheet, but for some reason it's not working. You will also learn how to check, find and remove circular references in Excel worksheets, and if none of the above is an option, how to enable and use circular formulas. This short tutorial explains the basics of Excel circular reference and why you should beware of using them.
0 kommentar(er)
