Does Excel need optimization ?
Generally speaking no. The normal use of excel with a few sheets and straightforward formulas does not need any optimization. However, sometimes workbooks grow, especially if you try to have all related data in the same place. Also when you start creating User defined functions without care to whats good and whats bad, you can end up with long run times.
Excel re-calculates any dependent formulas whenever you change a cell automatically by default. Once the recalculation times start to get high, there is a temptation to turn recalculation to manual. When this happens it leads to mistakes and sometimes wasted effort in trackin down problem with formulas that aren’t really there.
It’s to deal with this – the identification of which areas of spreadsheet need optimization – that I developed the Workbook Analyzer.
Identifying choke points in your user defined functions can be time consuming and frustrating. To help you profile what’s taking time to process, you can use the procedure profiler.
Automatic code profiling
For a really quick start, you can insert and remove profiling code automatically using the automatic profiler
These capabilities introduce data structures that you can access for a really deep dive into your code. Take a look at some examples at Advanced profiling: Rogue Use Cases and the new classes available to you cProcProfiler and cProcTimer.
These are available at the download page