The Circuit Breaker – How to Fix Circular Reference Errors in Excel
If you’ve spent much time working with elaborate Excel workbooks that rely on iterative calculations to solve formulas with circular logic, you’ve undoubtedly come across a problem where if you accidently put an erroneous value in an input cell that the circular logic relies on, the entire workbook errors out permanently.
This can be incredibly frustrating because the only solution to fix the problem is to close the workbook and start over, oftentime losing minutes or hours worth of work. However, there is a solution – the Circuit Breaker – that if built into your model will save you a lot of time and frustration.
Background – How I learned this Circuit Breaker Trick
I learned this trick (Excel hack if you will) from a friend, Matt Robinson, while working together in real estate acquisitions a while back. Our team had a development model that relied on circular logic and iterative calculations for modeling construction interest. Unfortunately, every week or so, one of the members of the team would put in a value that the model didn’t like and the whole model would blow up. The solution Matt taught us was to add a “Circuit Breaker” to the model. The circuit breaker essentially resets the circular calculations, thus fixing the model and allowing you to continue from where you’d left off.
I’ve since used this tip many times, adding it to every model I build with iterative calculations. In 2015, I shared this Circuit Breaker fix by way of a ‘How-To’ video. So here it is: How to Fix Circular Reference Errors in Excel using the Circuit Breaker.
- Click here to download the file that contains the Circuit Breaker feature used in this video
Update 2023: Why I Now Avoid Circular References Altogether
While this circuit breaker trick still works (and some of you have no other choice than to implement it), I’ve personally since gone away from using ‘Iterative Calc’ in all of my models. There are a few reasons why, that I thought I’d share with you:
Endless Loops
One of the main risks is the possibility of creating an endless loop. If you don’t limit the number of iterations or the amount of acceptable change between calculations, Excel may continue to iterate indefinitely. This could lead to program freezing or crashing. Or even worse, it will stop iterating and return answers – that may or may not be correct.
Inaccurate Results
If you’ve set the maximum iterations or maximum change levels too high, Excel may stop calculating before it reaches an accurate solution. Conversely, if you set them too low, Excel might take an excessively long time to perform the calculations or not return a result at all. I’ve seen iterated answers that were WAY off.
Resource Consumption
Performing iterative calculations can consume a lot of computational resources, depending on the complexity of the calculations and the size of your Workbook. This might slow down your (or other users’) computer significantly.
Difficulty in Debugging
If there are errors or inconsistencies in your results, it can be challenging to debug the issue because the calculations are iterative. This can make your spreadsheets hard to manage, particularly for larger and more complex data sets.
Potential for Misinterpretation
If other users are not familiar with iterative calculations, they may not understand how your spreadsheet works, leading to potential misinterpretation of data.
Unintentional Circular References
Enabling iterative calculation also means Excel will stop notifying you about circular references. You could accidentally create a circular reference without realizing it, leading to potentially incorrect results.
Propagation of Errors
If there’s an error in a cell that is part of the iterative calculation, this error will get propagated through each iteration, creating a cascading effect (i.e. the reason for a Circuit Breaker in the first place). These errors can quickly compound and lead to significantly incorrect results. Since the calculations are performed repeatedly, the error isn’t isolated to just one cell but affects the entire chain of calculations. The end result is a Workbook that is broken!
While iterative calculations can be useful in certain scenarios, they need to be used with caution due to these potential issues and risks. I personally have made the decision to avoid them altogether, and in the rare cases when I can’t find a non-circular solution, I use generative AI to create Excel macros that provide the iterative solution.
