Part 1: Introduction – Part 2: Timer – Part 3: Volatile formulas – Part 4: Lookup formulas – Part 5: Sorting source data – Part 6: Table or List? – Part 7: Multiple criteria lookups – Part 8: Sum with multiple criteria – Part 9: Other factors – Part 10: Summary
Part 1: Introduction
A few months ago I created the most beautiful and dynamic dashboard I had ever created. There were selection options, automatically updating charts and the ability to drill into numbers. I was proud of what I had achieved with a little bit of VBA (I’ll save these techniques for a future post). But there was one big drawback . . . the calculation took 16 seconds. You heard me correctly . . . . 16 Seconds!!!!! Even with all the bells and whistles, if it takes 16 seconds to recalculate then it is not particularly dynamic, is it?
I had ignored one of the most fundamental aspected of dashboard design; how the data is stored and the formulas/techniques used to pull the data into the dashboard. I had not even considered how to optimize formulas for the best calculation speed.
Slow calculating spreadsheets
Have you ever experienced slow calculating spreadsheets? Who do you blame? If you’re like me, you blame Excel. It’s Excel’s fault for being slow. However, is it possible that you’ve not used the best formula for the job? And as a result, the calculations do not run as fast as they should.
If the battery compartment on the back of a child’s toy has a screw in it, what tool should you use? A screwdriver, right. But have you ever decided that it’s just too much effort to find a screwdriver, so maybe a table knife will work just fine? Often that’s true, it does work just fine. Now that we’ve established that you could use a table knife as a screwdriver that does not, therefore, make it the best tool for all screwdriver based activities. For a small simple task, it’s might be fine, but definitely not for all tasks.
How about your Excel formulas? Are you using the only formulas you’ve got to hand because you’ve not got the right tools to do it properly? Did you learn VLOOKUP, INDEX/MATCH or SUMPRODUCT and then decide that you will use those for everything? I did. First I loved VLOOKUP until I realized that it only returned the first result found, then I loved SUMIF as it returned all the results. But then I discovered SUMPRODUCT, it seemed to do everything. I just wanted a single formula that could do everything and SUMPRODUCT was that formula.
Then one day was designing my dashboard, and I wasn’t dealing with 1,000 transactions, I was dealing with 500,000 transactions. Instantly my beloved SUMPRODUCT formula was so slow and clunky.
Changing to Manual Calculation is not the answer
I resorted to the only option I could think of – I changed the calculation settings to Manual. Life was happy again, until the day somebody else asked to use the dashboard. I was embarrassed to let them use such a slow and clunky tool.
I’ve never been a fan of changing the calculation settings to manual. Not since the day I forgot to press F9 and I e-mailed utter rubbish to the CFO! A lot of Excel users change their calculation settings to Manual. It prevents a lot of wasted time waiting for the recalculation to complete. But, I now believe this should be the last resort. If you are relying on a user to press F9 then it is bound to not happen at some point.
There will always be some workbooks where manual calculation is necessary. This is acceptable when using an ODBC or other data link, or where the model has to be complex. But let’s make this the exception, rather than the rule.
Review of data tables and formulas
The best thing about Excel is that it can calculate thousands of things in a microsecond. It’s perfect for small dynamic dashboards. So, let’s get back to basics and learn the best ways to optimize our workbooks with the correct formulas and data layouts.
I managed to get my dashboard to recalculate in under half a second, just by optimizing the formulas. Over the next few weeks, I will try to tackle this topic in a variety of ways, so that we can all optimize our workbooks. Then we all stand a much better chance of keeping Automatic Calculation turned on.