MrExcel.com - Your One Stop for Excel Tips & Solutions

Consider Formula Speed

February 02, 2022 - by

Consider Formula Speed

Problem: My workbook is calculating really slowly. Are there ways to speed it up?

Strategy: Upgrade to 64-bit Excel. The 32-bit versions of Excel can only use 2 or 3GB of the memory on your machine. Adding memory beyond the 3GB that is already likely in your computer won’t help. When you convert to 64-bit Excel, Excel can use up to 8 Terabytes of memory.

Even if you have 64-bit Windows, the default is still for Excel to install as 32-bit. Here is how to check. Go to File, Account. Click About Microsoft Excel. Check the very top line of that dialog box. If your version number does not end in 64-bit, you are limited to 2 GB of memory usage.

At the end of the Version number, look for the words 64-bit.
Figure 313. 98% of all Excel installations are not taking advantage of 64-bit.

Problem: Our company uses an ancient add-in that still is not compatible with 64-bit, so we are stuck with 32-bit.

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 https://mrx.cl/excelspeed. Charles also sells the Fast Excel V3 utility which will analyze your workbook for bottlenecks. For details: Fast Excel.

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.

Two ways to do Running Total. One adds the previous running total to this row's sales. The other uses an expanding range: =SUM(B$2:B15191).
Figure 314. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jonathan Chng on Unsplash