Volatile Functions in Microsoft Excel

How constantly changing functions affect your spreadsheet

Volatility Ahead road sign over gray skies
 DNY59/Getty Images

Volatile functions in Microsoft Excel, Google Sheets, and other spreadsheet apps cause the cells in which the functions are located to recalculate every time the worksheet recalculates. Learn how volatile functions recalculate even if they, or the data they depend on, don't appear to have changed.

Note: These instructions apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Office 365, Excel Online, and Google Sheets.

Understand Volatile Functions

Any formula that depends either directly or indirectly on a cell containing a volatile function recalculates every time recalculation occurs. For these reasons, the use of too many volatile functions in a large worksheet or workbook increases the time required for recalculation.​

Screenshot of Excel showing common volatile functions

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

Volatile Function Examples

Some examples of volatile functions are found in the example below. In this example:

  • Cell D1 contains the =RAND() function. This generates a new random number with each recalculation of the worksheet.
  • Cell D2 contains the formula =D1+5. This makes cell D2 directly dependent on the value in cell D1.
  • Cell D3 contains the formula =D2-10. This makes cell D3 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

Each time worksheet recalculation occurs, the values in cells D2 and D3 change along with the value in cell D1. This is 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) in a worksheet.
  • Renaming a worksheet.
  • Reordering the worksheets within a workbook.
  • Adding, editing, or deleting a named range.
  • Pressing F9, while in manual recalculation mode, to recalculate cells that contain formulas or dependent formulas that have changed since the last calculation in all open workbooks.
  • Pressing SHIFT+F9, while in manual recalculation mode, to recalculate cells that containe formulas or dependent formulas that have changed since the last calculation in the active worksheet only.
  • Pressing CTRL+ALT+F9, while in manual recalculation mode, to recalculate all formulas in all open workbooks regardless of whether they have changed or not since the last calculation.
Formula calculation options in Excel

Automatic and manual recalculation options are found in the settings menu of Excel. To find the settings on a Windows PC, select File > Options. On a Mac, select Excel > Preferences. Change the settings to fit your preferences on the Formulas tab in the Calculation options section.

Conditional Formatting and Recalculation

Conditional formats are 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.