Tip # 37 Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

• Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
• Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula. (See Tip #1 for an alternate suggestion.)
• Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
• Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.