Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 67 Reduce the 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-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.