XL: Maximum Array Size in Microsoft Excel

ID: Q75376

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a

SUMMARY

In Microsoft Excel versions later than versin 3.0, the maximum size of an array is 64 KB. In Microsoft Excel version 7.0 (a 32-bit program), this is approximately 5,458 elements. In Microsoft Excel versions 3.0, 4.0, and 5.0 (16-bit programs), this is approximately 6,550 elements.

NOTE: In Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition, there is no effective limit to the size of an array.

MORE INFORMATION

Page 166 of the "Microsoft Excel User's Guide 1," version 4.0 and page 288 of the "Microsoft Excel User's Guide" for version 3.0 state that Microsoft Excel allows arrays of up to approximately 6,500 elements. In addition, the "Features and Specifications" section in Appendix A of the "Microsoft Excel User's Guide" does not include the array size limit. The Microsoft Excel Online Help incorrectly states the limit as 1,636.

If you attempt to create an array on a worksheet that uses a greater number of cells than the number of elements allowed for that version of Microsoft Excel (5,458 in version 7.0, 6,550 in versions 3.0, 4.0, and 5.0), you receive the following error message:

Selection is too large

When you use an array that contains more than the maximum number of elements in a function, the function returns the "#NUM!" error value. The following is a list of some of the functions that use an array as an argument:

SUM(IF()), MINVERSE(), MDETERM(), MMULT(), TRANSPOSE(), LINEST(), TREND()

MINVERSE is the apparent exception to this rule. In Microsoft Excel version 4.0, MINVERSE allows only an array size of 3,249 elements (57 Rows by 57 Columns). If this maximum array size is exceeded, the function returns the #VALUE! error value.

For additional information, please see the following article in the Microsoft Knowledge Base:

Q132221 : XL7: Array Formulas Return #NUM! Error Value

REFERENCES

"User's Guide," version 5.0, page 171
"User's Guide 1," version 4.0, page 166
"User's Guide," version 3.0, page 288

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.00 4.00 4.00a 5.00a 5.00c publish subscribe matrix matrices limit err msg XL98 XL97 XL7 XL5 XL4 XL3

Keywords : xlformula
Version : MACINTOSH:2.x,3.x,4.x,5.0,5.0a; WINDOWS:2.x,3.x,4.x,5.0,5.0c,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbinfo