Optimize Formula Calculation Speed – Part 9: Other factors

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 9: Other factors

So far in this series we have undertaken a number of tests to find out which formulas are the fastest and how best to layout the source data. But, in this part we will be considering a number of other factors, and how they impact calculation speed.

  • Does the location of the source data impact calculation speed?
  • Does referencing the whole column in a calculation range, rather than the specific range, impact calculation speed?

A lot of sites list these as considerations when trying to optimize calculation speed.  But how much impact do they really have?

If you want to use the workbook and find out for yourself you can download it here.

Location of source data

In relation to our formulas there are 3 places we can keep our source data:

  • Within the same worksheet
  • Within a different worksheet of the same workbook
  • Within a worksheet of a separate workbook

The general advice to optimize calculation speed  is to keep the data within the same worksheet as the formulas.  Or, at the very least ensure it is in the same workbook.

Excel Sumproduct calculation times with data in different sources

The results

Look at the results above, the times are all very similar.  Based on this test it actually makes very little difference where the data is stored.  The slowest calculation occurs when the data is a different workbook.  However the impact is only 2%, so probably not a key focus area when trying to speed up calculations.

Referencing the whole column, rather than the specific cells

A number of formulas can be written so that they reference specific cells, or whole columns.  SUMIF is probably the best example of this type of formula.  Other articles state that calculation speed is impacted when referencing more cells than required.

Excel Sumif calculation times with different range selection

The results
There is a difference in the times between the two scenarios – approximately 4% difference.  So, whilst there is a speed boost, it is not very significant.

Conclusion

Whilst it is correct that data source location and having a specified range of a formula can have an impact on speed, the speed impact is only 2% – 4%.  If you are experiencing slow calculating formulas, making changes to your worksheets for these is unlikely to solve the issue.  However, when setting up a workbook it would be useful to plan so that these are not an issue.

A little accidental discovery – how does Battery Power or Mains Power impact speed?

Whilst creating this series, I noticed I was getting different calculation speeds depending on whether my laptop was running on battery power or mains power.  I had not really considered this to be an issue before.  But I thought I would test the impact.

Excel Sumproduct calculation times with different power source

As you can see from the table above, there is approx 14% speed increase from having a laptop plugged in.  Is this specific to my laptop?  Possibly.  I don’t know. But it is worth testing, if you do have slow calculating workbooks.

Next time

In the final part we will be bringing together all the learnings from this series.  We compare which formulas or methods really create the biggest speed difference.

Save

Leave a Reply

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