Volatile Functions in Microsoft Excel and Google Sheets

How Constantly Changing Functions Affect Your Spreadsheet

Volatility Ahead road sign over gray skies
 DNY59/Getty Images

Volatile functions are functions in Microsoft Excel, Google Sheets, 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.

Screenshot of Excel showing common volatile functions

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

Some of the more commonly used volatile functions are the NOW, TODAY, and RAND functions.

Volatile Function Examples

  • 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.
Screenshot of Excel showing volatile function examples

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 cell 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
Screenshot of Excel showing calculation options

Automatic and manual recalculation options can be found within the settings menu of Excel. Windows PC users can access the option via File > Options, and Mac users can do so via Excel > Preferences. The particular setting is located under the Calculation section.

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.