Improving Calculation Performance in Excel–Final
January 10, 2011 Leave a comment
There would be no final for performance tuning, as we always want faster models and I have enough tips to cover up to part 99. But let’s stop here and I wish we learn the lesson that performance should be considered in the design on every Excel model. Performance does not come by accident; we have to carefully craft it. No one want to spend more than 10 seconds for the results and don’t even mention hours or days. We don’t always need a faster machine and sometime even a faster machine won’t help a lot. There are many useful resources that we can Google about and we would find most of them involve common sense. We don’t have to have a PhD in computer science to understand the concepts; mainly remove unnecessary calculation and use a faster method if available.
How can we determine what method is faster? We can use timer in Excel VBA but what about Excel functions? There is a very useful set of macros that we can find in a Microsoft MSDN article, Excel 2010 Performance: Improving Calculation Performance written by Charles Williams, Decision Models Limited. This article also discuss many great tips about performance tuning in Excel and you may want to visit Decision Models’ website because Charles has a wonderful profiling tools, FastExcel, to find and measure timing and memory bottlenecks in our workbook.
Let’s go back to the example in Improving Calculation Performance in Excel–Part 2 and illustrate how this macros can help us.
- Select the range that we like to measure the performance, i.e. C2..C13
- Press Alt+F8
- Select RangeTime and click Run button
- Now, we can see how long Excel took to perform the calculation.
- And let’s repeat it on Col D
Only 12% gain? Not a lot! Well, it is because we only sums up 12 records. Let’s see the performance gain when we have 1000 records. It is 2.4 times faster!
What if we push further and review 5,000 records, then you can see the HUGE difference, i.e. 12 times faster!
How about the maximum limit of Excel? 1,048575 records. Col D returned the results in less than 2 seconds. How about Col C? I went out to do 30 minutes jogging, came back to have a quick shower and breakfast… Excel is still very busy doing calculation, so I stopped it after an hour.
Col C is not scalable nor sustainable. Initially, it may work and give us the correct results but as we have more data, the processing time would increase exponentially. The fix (Col D) is very simple and effective that any Excel user can understand.
There may be time that we have to involve an IT consultant who can optimize our Excel model. But if we do our homework then we may find ways to significant improve the performance of our Excel models.
Wish you all the success in performance tuning. But if you need any help then please don’t hesitate to contact me at 416-804-6878 or chan_a@algconsultings.com
Andrew Chan is the owner and founder of ALG Inc.
We help you to make better and faster decisions!
About Andrew Chan
Go Beyond Excel 