Excel Add-ins, Excel Models, Excel Tips, Real Estate Financial Modeling, Tutorial

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.

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 the spirit of the Holiday season, I thought I’d share the 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.