XL97: Maximum Array Size in Microsoft Excel 97

ID: Q166342

The information in this article applies to:

Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel 97, the "Worksheet and workbook specifications" Help topic states that the maximum size of an array in a worksheet is 6,553 elements. This information is incorrect. This article explains the limitations of arrays in Microsoft Excel 97.

MORE INFORMATION

In Microsoft Excel 97, arrays in worksheets are limited by available random access memory and by the "entire column" rule.

Available Memory

Unlike earlier versions of Microsoft Excel, Microsoft Excel 97 does not impose a limit on the size of worksheet arrays; you are limited only by the amount of available memory on your computer. Because of this, you can create very large arrays that contain hundreds of thousands of cells.

The "Entire Column" Rule

Although Microsoft Excel 97 allows you to create very large arrays, it does not allow you to create an array that uses an entire column or multiple columns of cells. This limitation is designed to prevent you from creating an array that uses an entire column of cells. Because recalculating an array formula that uses an entire column of cells (there 65,536 cells in a column) is somewhat time consuming, Microsoft Excel 97 does not allow you to create this kind of array in a formula.

Earlier Versions of Microsoft Excel

For more information about the limits of worksheet arrays in earlier versions of Microsoft Excel, please see the following article in the Microsoft Knowledge Base:

Q75376 XL: Maximum Array Size in Microsoft Excel

Array Formula Examples

The following is a list of array formula examples:


   A1: =SUM(IF(B1:B65535=0,1,0))
   A2: =SUM(IF(B:B=0,1,0))
   A3: =SUM(IF(B1:J65535=0,1,0))
   A4: =SUM(IF(B:J=0,1,0))
   A5: =SUM(IF(B1:DD65535=0,1,0))

NOTE: To use these examples, create a new workbook. Note that you must enter each formula as an array formula. To do this, type the formula in the formula bar, and then press CTRL+SHIFT+ENTER to enter the formula.

Array Formula Results:

The formula in cell A1 returns the result 65535. This result is correct.

The formula in cell A2 returns a #NUM! error because the array formula refers to an entire column of cells.

The formula in cell A3 returns the result 589815. This result is correct.

Note that the formula may take a long time to calculate the result because the formula is checking almost 600,000 cells.

Like the formula in cell A2, the formula in cell A4 returns a #NUM! error because the array formula refers to an entire column of cells.

When you enter the formula in cell A5, you may receive the following error messages:

Not enough memory.
Continue without Undo?

-and-

Not enough memory.

In this case, the size of the worksheet array is too large for available memory, and the formula cannot be calculated. Also, because your other formulas must recalculate their results, Microsoft Excel may appear to stop responding for a few minutes. After the results are recalculated, Microsoft Excel should respond normally. The formula in cell A5 returns the value 0 (zero).

Note that none of these formulas work in earlier versions of Microsoft Excel because the worksheet arrays created by the formulas are all larger than maximum limits in earlier versions.

Additional query words: XL97

Keywords : kberrmsg xlformula
Version : WINDOWS:97
Platform : WINDOWS
Issue type :