Excel Circular Reference
Circular reference in excel means referring to the same cell in which we are working, circular reference is a type of pop up or warning displayed by excel that we are using a circular reference in our formula and the calculation might be incorrect, for example in cell A1 if I write a formula =A1*2 this is a circular reference as in inside the cell A1 I used cell reference to A1 itself.
An equation in a cell that specifically or in a roundabout way refers to its very own cell is known as a circular reference. For instance, cell B1 underneath straightforwardly refers to its very own cell which is not possible. We can’t use the formula for the same cell in the very same cell.
It means that when you use the formula in the same cell which refers to the same cell, it created a circular reference and shows a warning that found one or more circular references.
When you got the above error message, you can click Help for more data, or close the message window by clicking either OK or the cross the dialog box which appears when it found a circular reference. When you close the message window, Excel shows either a zero (0) or the last determined an incentive in the cell.
How to Enable/Disable Circular References in Excel?
If you want to circular formulas to work, you must enable reiteration calculations in your Excel worksheet.
- Step 1: Go to file,
- Step 2: see at the last Click on Option.
- Step 3: Go to Formulas, see the calculation option at the top, and enable the Iterative Calculation. Then click Ok.
- Step 4: Once you turn on the Iterative Calculation option in the formulas tab. Please check and specify the options as below mention:
- Maximum Iterations: It indicates how often the equation ought to recalculate. The higher the number of emphases, the additional time the computation takes.
- Maximum Change: It determines the most extreme change between figuring results. The smaller the number, the more precise the outcome you get and the additional time Excel takes to ascertain the worksheet.
You can change the maximum iteration and maximum as and when required, it all depends upon you to make it customized.
How to Use Circular Reference in Excel?
Below are the given data.
- Step 1: Grand total the quantity of the items which are sold including the cell in which you are using formula also.
- Step 2: The cell which contains the total value returned to 41,300 instead of 413 once iterative calculation was enabled.
How to Find Circular References in Excel?
- To find a circular reference, first Go to the Formulas tab, click on the option bolt by Error Checking, and then go to Circular References and the last entered circular reference is shown here:
- Tap on the cell shown under Circular References, and Excel will convey you precisely to that cell.
- When you do this, the status bar will tell you that circular references are found in your exercise manual and show a location of one of those cells:
- If you find a circular reference in another excel sheet, it does not show cell reference only it shows “circular reference”.
- When you open the file again after saving then also it shows the warning message that one or more formula contains a circular reference.
Why Circular Reference is Not Recommended?
As you definitely know, utilizing circular references in Excel is a technique that is not recommended and not a methodology that you can use easily and helps to reduce your efforts. Aside from execution issues and a notice message showed on each opening of an exercise manual (except if iterative estimations are on), circular references can prompt various different issues, which are not quickly evident.
Try to ignore using circular reference in excel which is recommended by excel experts too.
- The arrangement joins, which implies a steady final product is come to. This is the alluring condition.
- The arrangement wanders, which implies that from emphasis to cycle, the contrast between the current and the past outcome increments.
- The arrangement switches between two values. For instance, after the initial iteration the outcome is 1, after the next iteration, the outcome is 10, after the following cycle, the outcome is 100, etc.
Points to Remember
- Most of the time, when you use more than one formula in excel with a circular reference; Excel doesn’t show the notification message over and again.
- While using circular reference, always check that iterative calculation in your excel sheet should be enabled otherwise you won’t be able to use circular reference.
- While using the circular reference, it may lead to slow your computer because it causes your function to be repetitive until a particular condition is met.
- Under Maximum Change Value, type the smallest number for circular reference to proceed. When the number is smaller, the more exact the outcome and the additional time that Excel needs to calculate a worksheet.
- When you open the file again after saving then also it shows the warning message that one or more formula contains a circular reference. Always take care of the warning and check that circular reference is enabled.
This has been a guide to Circular Reference in Excel. Here we discuss how to find and resolve circular references in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –