Part 1: Introduction – Part 2: Timer – Part 3: Volatile formulas – Part 4: Lookup formulas – Part 5: Sorting source data – Part 6: Table or List? – Part 7: Multiple criteria lookups – Part 8: Sum with multiple criteria – Part 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:

- Use helper columns to reduce the criteria to just one criteria column (Part 7 – Multiple criteria lookups & Part 8 – Sum with multiple criteria)
- Arrange source data in a table layout, rather than a list. Use the VLOOKUP/MATCH or INDEX/MATCH/MATCH formulas. We may need to use helper columns and helper rows to create the table. (Part 6 – Table or list)
- If helper columns are not an option, use SUMIFS formula rather than an array lookup formula to return a numerical value. If the data is unique it will only return a single value anyway. (Part 7 – Multiple criteria lookups & Part 8 – Sum with multiple criteria)
- Use INDEX/MATCH (Array) formula when returning unique results which are not values. (Part 7 – Multiple criteria lookups)

## 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.

- Speed up Excel in 17 Easy Steps and Calculate Faster (Professor Excel)
- Avoiding Mistakes that Make Excel Slow Down to a Crawl (Data Bison)
- Speeding Up Your Excel Formulas (Chandoo)
- Suffering from Slow Excel Spreadsheets? Try these 10 tips to SPEED-UP your Excel (Trump Excel)
- Excel 2010 Performance: Tips for Optimizing Performance Obstructions (MSDN)
- Excel 2010 Performance: Improving Calculation Performance (MSDN)

This was fantastic. Thanks for posting.

Hi Will,

Thank you for the comment. I appreciate the support and I’m glad you found the post useful. Hopefully this has saved you some time (there is nothing more frustrating that watching that blue wheel spin round and round).

Hi – Have you considered comparing these results to Get & Transform / Data Model / DAX which have become available recently in Excel.

Microsoft have re-developed the Excel Calculation engine recently. It should soon be rolled out to all 365 subscribers. At that point, I my refresh these posts as the calculation times are likely to change.

Having to click Refresh make Get & Transform a more ‘manual’ type calculation. Though, using the CUBE functions would be an interesting comparison, I might do that.