News and Blog Articles

The other day, while searching for solutions for a big model I’ve been passed, I came across this piece of Excel technical documentation.  I hadn’t come across it before.  There’s lots in there, some of it relating to the big spreadsheet I’ve been working on.

Here’s a summary of the things that jumped out at me that I reckon not every Excel user may be aware of.  Remember folks, this is technical documentation from Microsoft which talks about what you might attempt in Excel that could cause it to ‘run out of road’.  If you’re vaguely serious about your Excel, it’s all worth thinking about I’d say.

Calculations down the page should refer to calculations above them.  Calculations to the right should refer to calculations to the left.  The model’s logic should read like a book – top to bottom left to right.  Anything else is asking for some kind of trouble.

Stay away from circular references.  Stay away from Excel iteration.  There are other solutions.

Where at all possible, keep your work self contained and in one place. 

Avoid links to external workbooks (thus avoiding the potential for broken links to external workbooks).

We’re talking about the number of cells you’re pointing Excel’s functions towards.  Don’t e.g. add up more than you have to.  Keep the cells you need to refer to for a calculation as compact as possible.

Index is usually combined with a Match function.  That Match component gives you a ready-made opportunity for centralising some of the work Excel has to do.

For example, if you’re wanting to dump out a column containing all of September’s results, one single Match function (returning the month number of 9) is all you need to apply Index to all rows like this: Index([first row of data],[link to one cell containing one Match function showing the value of 9]).  If you used say Vlookup you would end up forcing Excel to look through a lot more data and do more work.

You can read more about that idea here: Excel Index.

OK I’m extrapolating a bit from what I’m reading (although the tech docs do say “Array formulas are forced to calculate all the cell references in the formula even if the cells are empty or unused” which doesn’t sound great).  But what I’m saying here is based on recent experience and lines up with what I read here in the – slightly less blunt than I am – tech docs. 

We’re all loving the new dynamic arrays.  Use them once.  Use them as the icing on a nice piece of small cake.  Have fun with their limited use.   

There’s an alternative for you.  If you use lots of them.  If you use them on massive quantities of data.  If you use them again and again in the same piece of work.  If you use them on top of each other.  If you do a few of those things at the same time there will be tears.  I promise you there will.  Everything will just slow down.

Old school (notice the comments about Sumproduct if you know about the powers of that one) is best.  Sure have fun with the new dynamic arrays.  But please, right now, make sure it’s a teeny tiny bit of fun. That’s all you should allow yourself.

Really, I’m serious (or, should I say, Microsoft is serious).  Those fancy dynamic arrays could slow down your model if you use them a little too much.

Seriously, stay old school with this stuff at the moment.  P-LEASE.

If you’re using multiple conditions, at the first step reduce the pool of data down as much as possible.  If you had a set of data for commuters in the UK, and wanted to hunt through it for people who live in Blackpool and commute to work by helicopter, filter all the car drivers out first (= most of the data set) before doing anything else.  That will mean Excel has to do less work at the next stage.

Don’t make them any bigger than they absolutely need to be.  Don’t fall in love with them to the extent you use them everywhere all the time.

There’s a better alternative to Sum($A$1:[the end of the row]), as described below.

…save it as .xlsb for a likely quick win.

Apply it to a few cells that you’re most interested in.  Don’t e.g. apply it to a whole tab or big areas of tabs.

…and don’t make a habit of putting formulas inside Names (don’t blame me, I’m just parroting what I read from Microsoft here).

…in terms of the hardware that’s installed.  If you’ve got a top quality gaming PC at home, but your spreadsheets are crashing on your work computer, maybe put in a special request to your IT department for an upgrade so the two at least match?

…do a few basic/ essential/ standard things to make sure it’s running as quickly as possible.  It sounds to me as if (according to Microsoft) everyone in the world should be adding these lines to their VBA code.

If you’re a slightly-techy Excel modeller, you may be interested to read the whole article here: Microsoft tips for optimising Excel performance.

I reckon, if you look at it (slowly and carefully) there’ll likely be a few new things in there for you.  Or, at least, things you were vaguely aware of before seeing them so starkly presented in black and white like Microsoft does for us here.

Thanks Microsoft for publishing this stuff so explicitly (stuff that we may not have been quite so aware of otherwise).  Forewarned is forearmed!  If your Excel spreadsheet is creaking under its own weight, some of the reasons might be sitting right here!