A volatile function is simply a function that will recalculate each time any action is performed in Excel, such as entering data, changing column widths, etc. (One of the few actions that will not trigger a recalculation of a volatile function is changing a cell's formatting, unless you do this via Paste Special... » Formats.)
Probably the most well-known of all volatile functions are the
TODAY and the
NOW functions. Because the
TODAY function returns the current date, and the
NOW function returns the current date and time, it is vital that both of them recalculate often. If you have a worksheet that contains many volatile functions, however, you could be forcing Excel to perform many unnecessary recalculations on a continuous basis. This problem can worsen when you have volatile functions nested within nonvolatile functions, as the formula as a whole will become volatile.
To see what we mean, assume you have a worksheet that is using the
TODAY function in a 20-column-by-500-row table. This will mean you have 10,000 volatile functions in your workbook when a single one could accomplish the same job.
Rather than nesting 10,000
TODAY functions within each cell of your table, in most cases you can simply enter the
TODAY function into an out-of-the-way cell, name it
TodaysDate (or just use the cell identifier) or another applicable name, and then reference TodaysDate in all your functions.
A quick and easy way to do this is to select the entire table and then select Edit » Replace... to replace
TODAY( ) with
TodaysDate in all your formulas.
You will now have one
TODAY function in place of the 10,000 you would have had otherwise.
As another example, say the first 500 rows of column B are filled with a relative formula such as
=TODAY( )-A1, and the first 500 rows of column A have different dates that are less than today's date. You are forcing Excel to recalculate the volatile
TODAY function 499 times more than necessary each time you do something in Excel! By placing the
TODAY function in any cell and naming the cell TodaysDate (or something similar), you can use
TodaysDate-A1. Now Excel needs to recalculate only the one occurrence of the
TODAY function, resulting in a much tidier performance hit.