3305 Main Street Ste 019 Vancouver, WA 98663

# What are volatile Excel functions?

Definition of "Volatile" in Excel

A volatile function is a worksheet function that can be used within an Excel formula just like many other functions that you may use on a regular basis. The key difference is that a volatile function will be recalculated whenever a calculation occurs on the worksheet regardless of whether the formula containing the volatile function needs to be recalculated. This behavior exists to account for the fact that the volatile function may return different results even if none of its predecessors (inputs) have changed. A couple of examples would be RAND() and NOW(). For example, NOW() returns the current date and time which is constantly changing. Since the function is identified as volatile, whenever any calculation occurs on the worksheet, any formula containing the NOW() function will be recalculated regardless of whether the proceeding calculation had any impact on the formula containing the NOW() function.

Use of Volatile Functions

Use of volatile functions should be limited. Particularly, basing other calculations on the result of formulas containing volatile functions can lead to a high number of unnecessary recalculations.

Affects of Volatile Functions
• Performance - Since volatile functions cause additional calculations to occur, the use (and particularly the over-use) of volatile functions can severely affect performance. For example, if the result of a formula containing a volatile function is used in other formulas which, in turn, feed other formulas, the effect can produce thousands of additional recalculations that are not necessary.
• Stability - There are known cases where an Excel crash can occur as a result of using volatile functions. For example, opening a CSV file will trigger the recalculation of volatile functions in a workbook that is already open. This scenario has been known to trigger a crash.
List of Volatile Functions
• AREAS*
• CELL
• COLUMNS*
• INDEX*
• INDIRECT
• INFO
• NOW
• OFFSET
• RAND
• ROWS*
• TODAY

* Function is documented as volatile by Microsoft but may not be volatile

Work Arounds
• Option 1 - Use static values in place of volatile functions. If the result of a volatile function is needed for another calculation, place the volatile function in its own cell and then use a macro to copy its value into a cell that will be referenced by the other formula.
• Option 2 - Using Visual Basic, create a non-volatile equivalent of the volatile function.
• Option 3 - Import a macro code module containing non-volatile equivalent functions.
7. In place of Excel's volatile functions, use the non-volatile versions. The non-volatile versions have the same name but are prefixed with "NV". Example: `=NVRAND()`