Howdy folks. Jeff here. I recently gave a presentation on Excel efficiency to a bunch of analysts, in which – among other things – I’d pointed out that if you ever find yourself having to switch calculation to Manual, there’s probably something wrong with your spreadsheet. Here’s the slide:
 

This prompted one of the participants to come to me for advise regarding restructuring a spreadsheet with that very problem. This analyst had a file with only 6000 rows of data in it, but the file size was something like 35MB, and after each and every change she had to wait at least a minute for the file to recalculate before she could do something else.

It turns out there were two problems with her files that were easy to resolve.

The Confused range

First, there was a problem with the Used Range – the area within a worksheet that Excel thinks contains all your workings and data. You can find out what this is for each spreadsheet by pushing [Ctrl] + [End], and seeing what cell this takes you to. Hopefully it will take you to the bottom-most, right-most cell that you’ve actually used in the sheet:

But occasionally, you’ll see that it might take you far, far below that cell. Maybe all the way to the very bottom of the grid:

 
This is bad. Why? Because when Excel saves a file, it includes information about things such as what type of Cell Formatting is used within the used range. If the used range includes millions of cells that aren’t even used, then the information that Excel saves regarding these cells can really blow out the file size. This is exactly what had happened in the case of the spreadsheet concerned. After we reset the used range, the filesize plummeted from 35MB to around 2MB.

Often you can reset the Used Range simply by selecting all the the empty rows under your data, and then deleting them. To do this, select the entire row immediately below your data, then press [Ctrl] + [Down Arrow] to extend the selection right to the bottom of the sheet, then right click and select Delete:

Note that you’ve got to use the Right-Click>DELETE option, NOT the Delete key on the keyboard. Pushing that Delete key does not reset the used range. In fact, this is often why the used range is wrong…it still reflects some data that used to be in the sheet, but that the user subsequently deleted using the keyboard.

When you’ve done this, then push [Ctrl] + [End] again and see where you end up – hopefully at the bottom right corner of your data.

Sometimes this doesn’t fix the problem, and you still find yourself well below your data. In this case, a bit of VBA will usually suffice. I’d suggest putting the below code into your Personal Macro Workbook, for times like this:

Sub ResetUsedRange()

    Dim sht As Worksheet

    Dim lng As Long

    For Each sht In ActiveWorkbook.Worksheets

        lng = sht.UsedRange.Rows.Count

    Next

End Sub

To see what to do with this code, read What would James Bond have in his Personal Macro Workbook.

Too much SUMIF

The second problem is that each file contained something like 60,000 SUMIF formulas in them. And each one of these formulas referenced two entire columns, rather than just the 2500 rows that actually contained data. It’s really easy to see just how big a problem you might have, simply by doing a Find All for the name of the particular function you’re after:
 

You can throw 60,000 VLOOKUPS or IF statements or other run-of-the-mill functions at Excel and it won’t even blink. But 60,000 resource-intensive number-crunching functions such as SUMIF, SUMPRODUCT, COUNTIF etc pointed at very large ranges will cause Excel to flinch, if not shut it’s eyes completely for large periods of time.

That’s because these functions are like Ferrari’s…very powerful, but very expensive. One SUMIF is going to travel very fast down the highway. A few hundred SUMIFS on the same stretch are still going to whiz by pretty fast. Tens of thousands of them are just going to crash in to each other:
 

 
(The image above comes from this New York Times article detailing a spectacular traffic pileup in Japan in 2011 that left a highway strewn with the smashed wreckage of eight Ferrari’s, a Lamborghini and three Mercedes sports cars. No-one seriously hurt apart from severely injured pride and a marked increase in insurance premiums the following year.)

Often you can use a PivotTable to do the same thing as a whole bunch of functions like SUMIF, COUNTIF, SUMPRODUCT et cetera. PivotTables are natural aggregation and filtering tools. In this case I could use just one PivotTable to replace those 60,000 SUMIFs, and recalculation time dropped from minutes to milliseconds. Now, reporting on this business process is effortless.

One spreadsheet, two morals

I’ve got two morals to share regarding this.

The first is to keep your eyes peeled for signs of trouble in your spreadsheets. Think of FileSize and Recalculation Time as the rev-counter of your car…if it’s getting further and further into the red, then pull over, and check under the hood.

The second – and I can’t underscore this enough – is the importance to organizations of educating all users on how to recognize symptoms of inefficiency. They don’t all have to know how to treat it (although that would be good), but just how to diagnose it. Because if it goes undiagnosed, avoidable inefficiency imposes significant, on-going, and very real opportunity cost. A real dollar amount.

Raising awareness of danger signs is possibly the biggest efficiency gain and risk-reducing opportunity that any training initiative can offer, at the least cost. It’s a game-changer.

Two morals, multiple remedies.

Over at the Daily Dose of Excel blog, I recently posted a mock business case centered around corporate investment in Excel training programme. There’s much more food for thought there, and even more in the comments, so go take a look, and please do leave a comment there with your own thoughts.

While this business case revolves around an internal corporate training programme, another great way of reducing this opportunity cost is through courses such as Chandoo.org’s own Excel School, VBA Classes, and other Chandoo courses.

Not to mention other fantastic courses that you’ll find advertised on the web if you look.

And yet another is though interactions in places like the Chandoo Forum, where you’ll find an army of ninjas with more collective experience than the Borg from Star Trek. The hive mind that is a forum knows no equal.

And of course, you’ll find a wealth of information on this very blog, in articles like I said your spreadsheet is really FAT, not real PHAT!

About the Author.

Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:

=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))

That’s right, pure #VALUE!

Find out more at http:www.heavydutydecisions.co.nz