Excel Volatile Functions and Performance

Frequently Asked Questions

Excel Volatile Functions and Spreadsheet Performance

What are volatile functions in Excel?

Volatile functions in Excel are functions that recalculate every time any change is made to a worksheet, regardless of whether the change affects the cells referenced by the function. This behavior can significantly impact spreadsheet performance, especially in large or complex workbooks. Common volatile functions include OFFSET, INDIRECT, RAND, RANDBETWEEN, NOW, TODAY, INFO (in some cases), and CELL (in some cases). Source

Which built-in Excel functions are considered volatile?

The built-in Excel functions considered volatile are: OFFSET, INDIRECT, RAND, RANDBETWEEN, NOW, TODAY, INFO (in some cases), and CELL (in some cases). These functions trigger recalculation of dependent cells and can slow down performance. Source

How do volatile functions impact Excel performance?

Volatile functions recalculate every time any change is made in the spreadsheet, even if the change is unrelated to the function. This can cause a chain reaction, triggering recalculation of all dependent cells and making large spreadsheets sluggish or barely usable. If multiple Excel files are open, volatile functions in all files are recalculated whenever a change occurs in any file. Source

What is the Offset function in Excel and how does it affect performance?

The Offset function returns a cell or range based on a starting cell and specified row and column offsets. It is volatile, meaning it recalculates with every change in the workbook. While Offset itself does not take significant time to calculate, its volatility can trigger recalculation of dependent functions (like VLOOKUP), which may slow down overall performance. Source

How can replacing Offset with Index improve Excel performance?

Replacing Offset with Index can improve performance because Index is a non-volatile function. Index only recalculates when its referenced cells change, reducing unnecessary recalculations. For example, replacing =OFFSET(Sheet1!$A,B3,C3) with =INDEX(indextable,B3+1,C3+1) can help optimize spreadsheet speed. Downloadable example files are available for hands-on comparison: Offset example and Index example. Source

Where can I find more information or download example files comparing Offset and Index?

You can download two example Excel files for hands-on comparison: Offset example file and Index example file. For detailed explanations, visit the original article.

Excel Volatile Functions and Performance

Excel expert and solutions architect Dany Hoter discusses how to improve spreadsheet performance by avoiding volatile functions.

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

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)

=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

Open the Offset file.

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