Optimize formula calculation speed – Part 2: Timer

Excel Optimize Formula Calculation Speed

Part 1: Introduction – Part 2: Timer – Part 3: Volatile formulas – Part 4: Lookup formulas – Part 5: Sorting source dataPart 6: Table or List? Part 7: Multiple criteria lookups – Part 8: Sum with multiple criteriaPart 9: Other factors Part 10: Summary

Part 2: Timer

In Part 1: Introduction we started to consider the topic of how to Optimize Formula Calculation Speed.  Far too often we resort to turning calculation to manual. As a result, we are increasing the risk of issuing uncalculated documents.

The best way to measure success in optimizing formula calculation speed is to measure the time it takes to complete the activity. Thankfully, Microsoft has provided a MicroTimer code, which is much more accurate as it utilizes the system timer

Microsoft’s – MicroTimer VBA code

The code for the MicroTimer can be found in this article Excel 2010 Performance: Improving Calculation Performance.  Find the two sections of code half way down the document and copy them both into a new VBA module.

To create a new VBA module:

  • Press ALT + F11.  The VBA window will open.
  • Insert -> Module
  • Copy the code into this module, close the VBA window.

Testing the MicroTimer

To test the MicroTimer, enter any data and formulas into one of the worksheets.  Press ALT+F8.  The Macro window will open.  You should see the 4 options.

Excel VBA Micro Timer

  • FullcalcTimer – Time the calculation of every formula in the entire workbook
  • RangeTimer – Time the calculation of the selected cells only
  • RecalcTimer – Time the calculation of any changes since the last recalculation, including any volatile formulas
  • SheetTimer – Time all the calculations on the active sheet

Select the option you wish and click Run.

The message box will tell you how long it will take to calculate.

Excel VBA MicroTimer Message

Recording calculation times

You will notice that when you run the MicroTimer over and over on the same workbook, sheet or range the calculation time is not the same.  Therefore it is best to run it multiple times (say 5-10 times) and then average the result.

Finding Bottlenecks in Calculations

Now comes the fun part.  Finding out what is causing the worksheet to calculate slowly.

  • Reduce the interference from other applications and formulas.
    • Close everything, except the workbook you wish to test.
    • Set the calculation mode to manual  (Formulas -> Calculation -> Calculation Options -> Manual)
  • Run the FullcalcTimer.  Make a note of the average time – this is the slowest recalculation as it recalculates everything.
  • Run the RecalcTimer.  Make a note of the average time – as there have been no changes to the workbook there should not be any new calculations to perform.  Excel will evaluate all the formulas.  The volatile formulas will be recalculated along with any formulas dependent on those volatile formulas.  This should be the fastest full recalculation.  If this re-calculation time is slow then you may be able to speed up the calculation be removing some volatile formulas (more on that next time)
  • Activate each sheet and run the SheetTimer.  This will identify which worksheets are running slowly.
  • Once the slow running worksheets have been identified use the RangeTimer on various columns, rows, ranges and cells to identify exactly which cells are causing the problems.

Where do you go from here?

Hopefully you will now know which formulas are causing your workbooks to calculate slowly.  Next time we will start looking at what changes we might make to our formulas to optimize the calculation speed.

Leave a Reply

Your email address will not be published. Required fields are marked *