Circular Reference in Excel: Stop Running in Circles

When working with Excel formulas on a daily basis, chances are you’re getting some problems from time to time, but if you were to make a top of the most frustrating errors Excel throws at you, circular reference errors definitely get a spot on the podium. What makes these errors so annoying is that Excel just tells you that you have a circular reference somewhere in your formula, without any indication to where it is or what you can do about it; basically, it’s just like getting lost in the desert and having someone approaching you just to say something along the lines of “Hey, I just want to let you know that you’re lost. Bye now.”

Fortunately, circular reference errors are fairly easy to track and fix, and this article will teach you how to do just that; if you want to learn more about Microsoft Excel formulas, check out this online course.

Understanding Circular References

A circular reference is when the formula in a certain cell makes reference to that exact same cell, directly or indirectly, thus creating an endless loop. Here is a simplified example of a circular reference:

This is the error you will get when trying to run the formula above:

In case you haven’t figured it out already, Excel tries to sum up the values in cells A1, A2, A3 and A4, insert the result in cell A4, then start over. This would basically lead to an endless loop of additions or, more likely, Microsoft Excel crashing. The easiest way to see whether an Excel workbook contains any circular references is to go to the Formulas tab, open the Error Checking drop-down menu and selecting Circular References; Excel will display all the cells that contain circular references. This online course on Excel productivity will allow you to find your way through Excel’s menus and submenus and configure certain parameters more easily.

While at a first glance it may seem that circular references are a complete waste of time, the truth is that they can be quite useful, if configured properly.

Setting up Circular References

While the formula above was an overly-simplified example of a circular reference that would have no real use in the real-world, a more complex version of a circular reference can be quite useful. The first step towards configuring circular references is to enable iterative calculations. You can do so by clicking on the Ribbon/File button, go to Options and, from the menu on the left pane, click on Formulas. Check the Enable iterative calculations checkbox; the two customizable values in that pane, Maximum Iterations and Maximum Change, are the key to making circular references work for you: the former defines how many times will the formula be executed, while the latter defines the maximum acceptable difference between results. The condition that is met first represents the trigger that will make Excel stop the calculation.

In the example used above, if the number of iterations is set to 3, Excel will run the formula 3 times, as follows:

• First run: sums the values in cells A1, A2 and A3, with the result 6 being inserted into cell A4;
• Second run: sums up values in cells A1 (1), A2 (2), A3 (3) and A4 (6), the result being inserted in cell A4 – 12;
• Third run: sums up the values (1+2+3+12) one more time, the end result displayed in cell A4: 18.

If you were to set up the Maximum Change value to a value greater than 6, Excel would only run the formula once, because the difference between the result of the first iteration and the second one would be greater than 6.

These two parameters define just how accurate your calculations are, but you have to keep in mind some aspects: the more runs are required, the longer it will take Excel to process your formulas.

So you just learned how to make use of one of Excel’s most controversial formulas, but do you master the simple ones?  Brigitta Schwulst wrote a very interesting blog post on the most basic Excel formulas everyone should know – you can find it here. If you’re already proficient in using Excel formulas, yet you’re looking to make Excel even more useful, learning to master macros and eventually VBA code is the next logical step, as it will allow you to automate a lot of repetitive tasks and create highly-complex formulas.

Get started with macros and Excel VBA by taking this two-part online course.