This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Consider Formula Speed
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.
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.
Figure 325 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.