The Use of Volatile Functions in Excel

Format
mla apa chicago
Your Citation
French, Ted. "The Use of Volatile Functions in Excel." ThoughtCo, Aug. 2, 2017, thoughtco.com/description-of-volatile-functions-3124106. French, Ted. (2017, August 2). The Use of Volatile Functions in Excel. Retrieved from https://www.thoughtco.com/description-of-volatile-functions-3124106 French, Ted. "The Use of Volatile Functions in Excel." ThoughtCo. https://www.thoughtco.com/description-of-volatile-functions-3124106 (accessed September 19, 2017).
Excel Volatile Functions
Excel Volatile Functions. © Ted French

Volatile functions are those functions in Excel and other spreadsheet programs that cause the cells in which the functions are located to recalculate every time the worksheet recalculates.

Volatile functions recalculate even if they, or the data they depend on, do not appear to have changed.

Further, any formula that depends either directly or indirectly on a cell containing a volatile function will also recalculate every time recalculation occurs.

For these reasons, the use of too many volatile functions in a large worksheet or workbook can significantly increase the time required for recalculation.​

Common and Uncommon Volatile Function

Some of the more commonly used volatile functions are:

while less commonly used volatile functions include:

Volatile Function Example

As seen in the image above,

  • cell D1 contains the =RAND() function, which generates a new random number with each recalculation of the worksheet
  • cell D2 contains the formula = D1 + 5, which makes it directly dependent on the value in cell D1
  • cell D3 contains the formula = D2 - 10, which makes it directly dependent on the value in cell D2 and, as a result, indirectly dependent on the value in cell D1

Therefore, each time worksheet recalculation occurs, the values in cells D2 and D3 will change along with the value in cell D1 because both D2 and D3 are dependent directly or indirectly on the random number generated by the volatile RAND function in D1.

Actions that Cause Recalculations

Common actions that trigger worksheet or workbook recalculation include:

  • entering new data while in automatic recalculation mode which is the default for all workbooks in Excel
  • adding or deleting rows or columns in a worksheet
  • hiding or unhiding rows (but not columns) within a worksheet
  • renaming a worksheet
  • reordering the worksheets within a workbook
  • adding, editing, or deleting a named range
  • while in manual recalculation mode, pressing the F9 key on the keyboard which forces, in all open workbooks, a recalculation of cells containing formulas or dependent formulas that have changed since the last calculation.
  • while in manual recalculation mode, pressing SHIFT+F9 on the keyboard which forces, in the active worksheet only, a recalculation of cells containing formulas or dependent formulas that have changed since the last calculation.
  • while in manual recalculation mode, pressing CTRL+ALT+F9 on the keyboard which forces a recalculation of all formulas in all open workbooks regardless of whether they have changed or not since the last calculation

Conditional Formatting and Recalculation

Conditional formats need to be evaluated with each calculation to determine if the conditions that caused the specified formatting options to be applied still exist.

As a result, any formula that is used in a conditional formatting rule effectively becomes volatile.