Excel: Efficient Use of Memory When Referencing a Range

SUMMARY

To minimize the amount of memory used by Microsoft Excel version 2.20 or 3.00, keep the data on a worksheet or macro sheet arranged in a rectangular fashion. Empty rows require no memory. However, partially filled rows use about as much memory as the full rows around them.

It is much more efficient to format, or make reference to, a row or column as an entire unit rather than as a large region of the unit.

For example, Excel makes much more efficient use of memory with "SUM(D:D)" than with "=SUM(D1:D16384)". If only a portion of column D actually contains data, then "=SUM(D:D)" requires only memory for those cells that contain data, plus a few bytes to represent the remainder of the column. With "=SUM(D1:D16384)", Excel allocates a few bytes of memory for every individual cell in the range, not just those that contain actual data.

MORE INFORMATION

This information was taken from page 157 in the "Macro Tips" section of the "Microsoft Excel Technical Reference for the Macintosh."

The "Microsoft Excel Technical Reference for the Macintosh" can be obtained as part of the Excel Development Kit. To obtain this kit, call Microsoft End User Sales and Service at (800) 426-9400