Volatile, Reference, Case Sensitive Functions

Volatile functions

Volatile functions are recalculated each time any cell in the spreadsheet is changed. Some other actions can also cause recalculation, such as renaming sheets, inserting columns, deleting rows, etc. Any formula that contains a volatile function will also become volatile. Any cell that depends on a cell that contains volatile function will also become volatile. Too many cells that contain a volatile function in a spreadsheet will significantly slow down that spreadsheet.

Volatile functions are RAND, RANDBETWEEN, RANDARRAY, NOW, TODAY, OFFSET, INDIRECT. INFO and CELL functions can be volatile depending on which arguments they use.

UDF functions can become volatile if the statement "Application.Volatile" is placed in front of them. The function below is nonvolatile.

Function UnVolatileFunction()
UnVolatileFunction = ActiveCell.Offset(-2, 0).Value + ActiveCell.Offset(-1, 0).Value
End Function

In contrast, this function is volatile because it contains an "Application.Volatile" statement..

Function VolatileFunction()
Application.Volatile
VolatileFunction = ActiveCell.Offset(-2, 0).Value + ActiveCell.Offset(-1, 0).Value
End Function

Excel Functions That Return References

Excel functions that return references are OFFSET, INDEX, XLOOKUP, CHOOSE, SWITCH, IF, IFS, INDIRECT. This means that the result of the function is neither a scalar nor an array. The result is the address of the range in the table.

Although the reference refers to multiple cells, Excel 365 or 2021 can directly display the result of these functions as a spill (1). In older versions of Excel, it was necessary to convert these functions into array functions with Ctrl + Shift + Enter in order to display their result directly (2). If a given range contains only one cell, all versions of Excel will return the contents of that cell (3). If a range contains more than one cell, it has always been possible to wrap that range in an aggregate function that will result in a scalar that can be displayed in a spreadsheet (4).

Case Sensitive Excel Functions

In Excel, there are three functions that are case sensitive. Those are EXACT, FIND and SUBSTITUTE. There are also XLOOKUP and XMATCH functions that have arguments that can be used to control whether the functions are case sensitive. Those functions are available in Excel 365 and 2021.

Direct comparison of A1 and B1 cells will return TRUE (1), but if we use EXACT function, result will be FALSE (2).