Volatile Functions in Excel
There are some functions that can generate a different result with each recalculation even when none of its arguments changed. Let’s see what the volatile functions in Excel are and how to handle them.
A list of Volatile Functions in Excel :
NOW
TODAY
RAND
RANDBETWEEN
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)
Volatile functions in Excel needs reevaluation each time your workbook recalculates. This means, regardless of the changes you make on your workbook, all of the volatile functions on your workbook will be recalculated with all their dependents.
Actions that causes recalculations are:
- Adding or deleting rows or columns in a worksheet
- Hiding or unhiding rows (but not columns)
- Renaming a worksheet.
- Reordering worksheets in a workbook.
- Pressing F9 in manual calculation mode (this will force recalculation of cells containing formulas in all open workbooks that have changed)
- Pressing Shift+F9 in manual calculation mode (this will force recalculation cells containing formulas in active worksheet that have changed)
- Pressing Ctrl+Shift+F9 in manual calculation mode (this will force recalculation of cells containing formulas in all open workbooks regardless of any change)
These functions can make your tasks easier at times but they have the potential of slowing your workbook significantly when used in large quantities. Therefore you need to approach these functions with caution.
