Excel Volatile Functions and Performance

In this article, I’ll explain a category of functions in Excel that are referred to as volatile.  

Volatile functions are recalculated every time you make any change to the spreadsheet. Even worse, if you have multiple Excel files open, and you make a change in one that doesn’t have volatile functions, all volatile functions in all opened files will still be calculated. Whenever one of these functions is calculated, it triggers the calculation of any cell that is dependent on the function, as well as those dependent on the dependent. As a result, using volatile functions can eventually make your spreadsheet barely usable.

Volatile built-in functions include:

  • Offset
  • Indirect
  • Rand
  • Randbetween
  • Now
  • Today
  • Info (in some cases)
  • Cell (in some cases)

For this article, I will focus on the popular Offset, which can be replaced in many cases by a non-volatile function.

The Offset Function

Offset can be used in one of two forms:

=OFFSET(Sheet1!$A$1,G5,H5)

  • This Offset function returns the value of a single cell. In this case, the two last arguments are optional. The returned cell in this example is G5 rows and H5 columns remote from A1.

=OFFSET(Sheet1!$A$1,0,0,G5,H5)

  • This example returns a range of G5 rows by H5 columns starting from A1. The range cannot be returned to a cell, but instead it can be consumed by another function like Sum or Count.

The performance gain can be achieved by replacing the first example of Offset with the Index function.

The Effect of Offset on Excel Overall Performance

The Offset function does not take significant time to calculate.

In my example, I combined the Offset function with other cells that use Vlookup. The value returned by Offset is used by Vlookup. The Vlookup functions, rather than Offset, are what make the overall performance sluggish.

Two Examples

You can download two files that are essentially doing the same job: One using Offset and the other using Index for the same purpose.

So if the original formula was:

=OFFSET(Sheet1!$A$1,B3,C3)

Or

=OFFSET(Sheet1!$A$1,B3,C3,1,1)

We can seamlessly replace it with

=INDEX(indextable,B3+1,C3+1)

Differences

The Offset function receives a single cell as first argument and returns the cell based on the distance from this cell in rows and columns. The Index function, by contrast, expects a range as the first argument that includes all the cells that might be returned. This forces you to determine the maximum size of the range that can be imagined, allowing for data growth.

Note that it is not expensive to exaggerate the size. Also, if the 1,1 values were used as the last arguments, they would need to be removed.

Difference in Performance

  1. Open the Offset file.
  1. Change the value in C3 from 2 to 1. It should take a few seconds for Excel to accept the value and calculate results. This is because all Offset functions are calculated on every change and as a result, all Vlookup functions are calculated as well.
  2. Now open the Index file. Apply the same change.

Wait—what happened? It took the same time! I managed to trick you. Remember that all volatile functions in all opened Excel files are calculated, so even when you change a cell in the index file, all the Offset functions are calculated.

  1. Close the Offset file and attempt the change in the Index file again. Now you will see that it doesn’t take any time at all.