Some Excel models have so many complex formulas that they just can’t calculate any more. These models often need to be re-created. Some models can be saved by making formulas more efficient. One technique is to add an OFF SWITCH to heavy formulas

 

Has your Excel model become a monster because it’s so big and slow? Tom Gauld’s comic says it all!

 

 

Re-build or Fix?

Over the years I have noticed many habits of spreadsheet users. Many work quickly adding formulas and data until the model becomes unbearably slow to calculate. At this late stage in the process they look for help.

Do I recommend re-building the model or can it be salvaged? Should parts of the model be re-built using VBA? Difficult to say until you dive in and see what’s going on.

 

 

Off Switch

Why Is An Off Switch Important?

I often find thousands of formulas that are calculating for no reason whatsoever. Adding an off switch can drastically reduce calculation time.

Don’t worry: it’s a smart off switch that will turn back on when needed.

 

Off Switch Example

Requirement: extract non blank values starting in column A moving down and to the right. This tiny example demonstrates how an Off Switch is helpful. Imagine a massive amount of data.

 

=COUNTA(A1:D6)

This light formula in cell G1 counts non blank entries in the range above. This is the Off amount.

 

=IF($G4>$G$1,””,SMALL(IF($A$1:$D$6<>””,COLUMN($A$1:$D$6)+ROW($A$1:$D$6)/1000,””),$G4))

Key part of formula in cell J4 is: IF($G4>$G$1,””  It compares cell G4 to the off value. Once the counter is greater than the off amount the formula stops (heavy array is ignored).

 

Why make Excel calculate to get the 20th value when we know it doesn’t exist? There are only 13!

If all 24 cells are filled in then the formula is turned back on and you’ll see all the answers!

You might have a simple formula dragged down 400000 rows waiting for data to be added later. The off switch technique can often help.

 

Download my Excel file here.

 

 

Further Reading

Excelxor’s post explains the off switch and why we shouldn’t wrap heavy formulas with IFERROR.

 

 

Tom Gauld

A special thank you to Tom Gauld for letting me use his comic. It was the perfect comic for this post! Tom is a cartoonist and illustrator. His work is often published in The Guardian, The New York Times and New Scientist.  I’ve followed his comics for a couple of years now and really enjoy them. I often take a break at work and scroll his Tumblr or Twitter feeds. Baking With Kafka is my favorite book of his! 

 

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’m a Data Analyst at McKinsey & Company.

Away from Excel I might be reading comics from: Tom Gauld, Roz Chast, Edward Steen, etc or playing with my dogs.

 

Check out my YouTube videos and blog.

Post a comment