, , , ,

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.


Frequently Asked Questions about Fixing Circular Reference Errors in Excel Using the Circuit Breaker

The Circuit Breaker is a technique used in Excel models that rely on iterative calculations. It “resets the circular calculations,” allowing the workbook to recover from errors caused by invalid inputs that break the model.

They occur when “an erroneous value in an input cell that the circular logic relies on” causes the iterative calculations to fail, often resulting in a full workbook crash or error state.

It allows users to “reset the circular calculations,” effectively clearing the faulty loop and returning the model to a functional state without closing or restarting Excel.

Spencer Burton learned the trick from a colleague, Matt Robinson, while working in real estate acquisitions. Their team faced recurring model crashes, and the Circuit Breaker “saved a lot of time and frustration.”

Spencer now avoids circular references due to issues like “endless loops,” “inaccurate results,” “resource consumption,” “difficulty in debugging,” and “propagation of errors.” He now prefers alternatives like using Excel macros or AI-generated solutions.

Risks include “endless loops,” incorrect or partial results, heavy “resource consumption,” loss of error notifications, and the potential to “create a cascading effect” of errors throughout the model.

With iterative calculations enabled, “Excel will stop notifying you about circular references,” making it possible to introduce one unknowingly and leading to potentially incorrect outcomes.

Yes, the blog post includes a link: “Click here to download the file that contains the Circuit Breaker feature used in this video.”

Instead of using iterative calculations, the author now uses “generative AI to create Excel macros that provide the iterative solution,” allowing for better control and reliability.


About the Author: Spencer Burton is Co-Founder and CEO of CRE Agents, an AI-powered platform training digital coworkers for commercial real estate. He has 20+ years of CRE experience and has underwritten over $30 billion in real estate across top institutional firms.

Spencer also co-founded Adventures in CRE, served as President at Stablewood, and holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.