Help! My Excel is too slow

Posted: 2020-10-14

Is your excel faster?

Quick tip for those who suffer with slow Excel syndrome (SES). The main culprit are typically too much data and large number of volatile functions. We can talk how to solve too much data in Excel the other day, but let’s take a look at volatile functions first.

These functions are considered volatile:

  • NOW()
  • TODAY()
  • OFFSET()
  • INDIRECT()
  • VLOOKUP()
  • HLOOKUP()
  • RAND()
  • RANDBETWEEN()

In case a worksheet has a deep calculation tree (for example a cell containing value from Today() function is input directly or indirectly to other cells) then any change on the worksheet will force Excel to rerun all calculations.

The solution to this problem is to replace Today with TodayNV. Here’s the VBA:

Public Function TodayNV()
Begin
	Application.Volatile False
    TodayNV = Today
End

This way TodayNV will not be calculated during every change, however in order to recalculate the cell containing TodayNV run either Ctrl+Alt+F9 (full worksheet recalculation) or press F2 and then Enter on that cell.

Other trick, especially when spreadsheet is heavily dependant on Vlookup or Hlookup functions is to replace them with combination of Match and Index. Here is an example:

Vlookup example:
Vlookup example
Match and Index example:

Vlookup example

Photo by Krzysztof Niewolny on Unsplash