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