This post may contain affiliate links. Please read my disclosure for more info:

Optimize Formula Calculation Speed – Part 10: Summary

Optimize Formula Calculation Speed

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 10: Summary

In this series, we have undertaken a variety of tests to identify what will provide us with the fastest calculating formulas.  Now it is time to bring all of those learnings together.  In this final part, we will try to focus on implementing the methods which will give us the greatest benefit.

 

Single criteria

Where there is a single criterion the following will help to reduce calculation times:

  • Use VLOOKUP or INDEX/MATCH for single criteria lookups (Part 4 – Lookup formulas)
  • Sort data only where the results looked up are weighted towards the records earlier in the source data set.  Do not sort data unless you have tested that it saves calculation time, as it is also possible for sorting data to slow down a calculation.  We need to consider this on a case by case basis (Part 5 – Sorting source data).  (This assumes we are not using an approximate match method).
  • Summarized data with unique records calculate faster than source data which requires a calculation (SUMIF for example) to be performed.   Therefore, if it is possible to summarize the data to the right levels first it can significantly reduce calculation time. (Part 4 – Lookup formulas compared with Part 8 – Sum with multiple criteria)

 

Multiple criteria

Where there are multiple criteria the following will reduce calculation times:

 

Other factors

Many other factors have a much smaller impact.  Focus on getting the formula and data layout right first, then consider other items which could be fine-tuned.

 

Other resources

There are other resources which may also give you ideas about how to optimize formula calculation speed.

Leave a Reply

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