Problem: My workbook is calculating really slowly. Are there ways to speed it up?
Strategy: Add more memory to your computer. Get a faster computer. Go to Excel 2010 with 64-bit with multiple CPU cores. Starting in Excel 2007, Excel will split the calculation chain and send a portion to each processor.
Problem: There is no budget for a faster machine.
Strategy: Read Charles Williams white paper on Formula Speed. This document has amazing ideas on how to be mindful of formula speed when building Excel formulas. The document is at http://msdn.microsoft.com/en-us/library/aa730921.aspx. Charles also sells the Fast Excel V3 utility which will analyze your workbook for bottlenecks. For details: http://tinyurl.com/fastexcel.
One concept in the article is moving a slow-calculating part of a formula out to a helper cell. If you have 1000 formulas that all divide by the same COUNTIF, you could move the COUNTIF to another cell and then have the 1000 formulas point to that one cell.
Another example is creating running totals. There are two choices here; use a formula of "add the cell above me to the cell to the left of me", also represented in R1C1 by =R[-1]C+RC[-1]. The other formula is the ultra-cool =SUM(E$2:E2). This formula's single dollar sign ensure that the range expands. Both provide the same answer.
- Both formulas provide the same answer.
Personally, I would always use the formula shown in Column D, because it is clever. In Charles' article, he points out that Excel only has to look at two cells to calculate cell C15191. It has to look at 15,190 cells to calculate cell D15191. With over 15,000 cells in the data set, this difference is dramatic. To calculate all of column C requires Excel to look at 30 thousand cells. To calculate all of column D requires Excel to look at 115 million cells. Column C will calculate in a miniscule fraction of the time of column D. If your worksheet is getting slower, check out Charles William's excellent article.