By Dany Hoter, DataRails Solutions Architect
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:
For this article, I will focus on the popular Offset, which can be replaced in many cases by a non-volatile function.
Offset can be used in one of two forms:
The performance gain can be achieved by replacing the first example of Offset with the Index function.
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.
So if the original formula was:
We can seamlessly replace it with
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.
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.
About the author:
Dany Hoter is a renowned Excel guru who works as a solutions architect at DataRails. Dany has over two decades’ experience working as an expert for the Microsoft Excel team, and his online Excel classes reach over one million students across the globe.