Excel: Creating Compact Models and Reducing Memory Usage

SUMMARY

If your Microsoft Excel worksheet model is getting too slow and cumbersome, and you want to reduce the amount of memory used on the worksheet, do one of the following:

1. Convert formulas into constants. Formulas require more memory than do constants. If you have inserted formulas that are no longer needed to calculate values on the worksheet, you can replace the formula with its values by using one of the following methods:

   a. Using Copy and Paste Special with Values selected

         -or-
   

   b. Activating the formula bar and choosing Calculate Now from the

         Options menu
   

2. Use array formulas to consolidate repetitive formulas. For example, if you need to place the products of A1 through C1 multiplied by A8 through C8 into cells A10 through C10, you can use less memory if you use "=A1:C1*A8:C8" as an array formula that is COMMAND+ENTERed into the range A10:C10.

3. Split the file into two or more linked files. Make sure you use simple external links with absolute references (for example, "=June_Sales!$A$5"). Microsoft Excel will update data from these links as it loads the sheet into memory. With appropriate use of linked files, the size of your model is limited only by available disk space.

MORE INFORMATION

These tips are in addition to all the other appropriate processes, such as turning off the RAM Cache, minimizing the number of desk accessories, and checking the position of the last active cell of your worksheet. To minimize the active area of the worksheet, delete any unused rows and columns and save the worksheet. Then close and reopen the worksheet.