Excel calculation efficiency
* Note: this is a backdated post from something I took notes on in the past
Some time back I had to do in depth research into excel efficiency. Some of the files we were using in banking were taking a long time to calculate, and we wanted to see what improvements could be made. There’s a lot of content out there, and these were the most helpful sites I found. Note that I’m not necessarily agreeing with all of the content though:

Microsoft’s own ‘improving performance’ writeup
 “With the Excel 2007 “Big Grid,” performance really matter”
 “Excel has three distinct phases in the overall calculation process:
 Build the initial calculation chain and determine where to begin calculating. This phase occurs when the workbook is loaded into memory.
 Track dependencies, flag cells as uncalculated, and update the calculation chain. This phase executes at each cell entry or change, even in manual calculation mode. Ordinarily this executes so fast that you do not notice it.
 Calculate all formulas. As a part of the calculation process, Excel reorders and restructures the calculation chain to optimize future recalculations.”
 “A volatile function is always recalculated at each recalculation even if it does not appear to have any changed precedents. Using many volatile functions slows down each recalculation, but it makes no difference to a full calculation.
 Some of the builtin functions in Excel are obviously volatile: RAND(), NOW(), TODAY(). Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO().
 Some functions that have previously been documented as volatile are not in fact volatile: INDEX(), ROWS(), COLUMNS(), AREAS().”
 Note: this may have changed in later office versions
 They give a list of volatile actions that trigger recalcs
 They also give a macro to measure calc time
 They give some golden rules to follow, with the general intent of simplicity
 Remove Duplicated, Repeated, and Unnecessary Calculations
 Use the Most Efficient Function Possible
 Make Good Use of Smart Recalculation
 Time and Test Each Change
 They give a neat example of how to simplify a problem
 And go through a long list of common bottlenecks with formulas

Another site with excel speed tips
 “Use Helper Columns”
 Strongly agree, and usually underutilised by many
 “Use Excel Tables and Named Ranges”
 Agree on named ranges, though I’m often too lazy
 “Use Faster Formulas Techniques”
 Notice the repeated advice
 “Use Helper Columns”

 “Isolate repeated formulae and move them to single cells”
 Related to the helper column point above
 “Nest if conditions in the order of frequency of occurrence”
 I can’t remember if this is true anymore actually, so take with grain of salt
 “Isolate repeated formulae and move them to single cells”

And lastly, a site comparing the speed of vlookup vs index match
 Index match is always superior for efficiency
 That said, I do still use vlookups when it’s a simple pull, or when I think my model is going to be looked at by someone who will be confused by index match. Design with end user in mind.