Reduce Impact of Volatile Functions on Recalculation

Volatile functions, which must be recalculated almost every time the user performs an action in Excel, can waste an enormous amount of time. Although volatile functions are too useful to discard entirely, there are ways to reduce the delays they create.

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-rowtable. 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 Find & Select → Replace..... under Editing options on the Home tab (pre-2007, go to Edit → Replace...) and 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. NowExcel needs to recalculate only the one occurrence of the TODAY function, resulting in a much lower performance hit.