You can create all sorts of mathematical models in Excel, but imagine a model with thousands of formulas, all of which need to be calculated every time you enter a value. This would make calculations very slow and the user experience, not a pleasant one. Thanks to its smart calculation logic, Excel calculates only dependent cells when you make changes to a cell. However, there is a set of formulas that don’t adhere to this rule, called volatile functions.
Volatile functions are calculated every time you enter data into any open workbook, even if that cell is not affected by any volatile functions. Because of this, using too many volatile functions in a large workbook can significantly increase the time required for running calculations. Let’s take a closer look at what volatile functions are and how they work. You might already be using some of these formulas in your models and may not even be aware of their volatile nature. Common volatile functions include,
- NOW()
- TODAY()
- RAND()
- RANDBETWEEN()
- OFFSET()
- INDIRECT()
- INFO() (depending on its arguments)
- CELL() (depending on its arguments)
When you think about it, these are the types of formulas that need to be calculated more often than others. For instance, NOW function would be almost useless if it displayed the current time, only when you first open the workbook. Similarly, conditional formatting formulas need to be evaluated with each calculation to make sure that the conditions are still fulfilled. Because of this behavior, conditional formatting formulas are also volatile.
Let’s take an example. You can either download our example and run it, or simply enter them yourself. Our example consists of the following,
- Cell B1 has “NOW()” function which returns the current date and time in number format.
- Cell B2 has “MINUTE(B1)” formula, which is dependent on cell B1 and contains non-volatile MINUTE() function.
- Cell B3 has “60-B2” formula which is dependent on cell B2.
B1 contains a volatile formula and it will update anytime it’s triggered. Since both cells B2 and B3 are dependent on cell B1, these two cells will also be updated, anytime B1 changes.
Formulas
First calculation
Second calculation
Our example workbook also consists of a user defined function created with VBA. User-defined functions (UDF) are not volatile by default. However, you can make a UDF volatile by adding Application.Volatile method into the code. For instance,
| Function MyUDF(MakeMeVolatile As Boolean) As Double
' It's a good practice to call this on the first line. Application.Volatile (MakeMeVolatile) MyUDF = Now End Function |
Remember to enable macros from Options>Trust Center>Macro Settings to test this application.
Auto calculations can be triggered by various actions. You may have noticed that volatile formulas calculate every time you enter data into a cell. Here is a list of other actions that will make Excel re-calculate all volatile formulas,
- Renaming or reordering worksheets
- Inserting or deleting rows or columns
- Hiding or showing rows (this doesn’t apply to columns)
- Some auto-filter actions
- Inserting, editing, or deleting a named range
- Double-clicking a row or column divider in auto-calculation mode
- When manual calculation mode is active, pressing the F9, SHIFT+F9, or CTRL+ALT+F9 keys to force calculations
- Third party tools that trigger a calculation with each iteration (i.e. Goal Seek)
Although volatile functions offer useful features, a complex model that is taking too long to calculate, might see a big improvement without them. If you have several volatile formulas in your workbook, it is advisable to switch to manual calculation mode by selecting it under the Formulas ribbon. These functions may not make much of a difference in most daily applications as these workbooks are typically small in size and not that complicated, but it is recommended to use them with caution if you’re not an advanced user.
