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 criteria 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 is 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.

 

What next?

Get Excel news, tips & tricks straight to you inbox.  Helping you to save time and achieve more with Excel.

You will also receive the Tab Hopper Add-in for FREE.

Tab Hopper Thumb







Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Leave a Reply

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