Maximum array size in Excel for Mac (181285)
The information in this article applies to:
- Microsoft Excel 2004 for Mac
- Microsoft Excel X for Mac
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q181285 SUMMARY
This article explains the limitations of arrays in Microsoft Excel for Macintosh.
MORE INFORMATION
In Microsoft Excel for Macintosh, arrays in worksheets are limited
by available random access memory and by the "entire column" rule.
Available Memory
Unlike earlier versions of Microsoft Excel, the versions of Microsoft Excel that are listed in the "Applies To" section do not impose a limit on the size of worksheet arrays. You are, however,
limited 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.
For additional information about how to adjust the memory settings for applications on the Macintosh prior to OS X, click the following article number to view the article in the Microsoft Knowledge Base:
141682
How to change memory allocations for Macintosh programs
With Microsoft Office applications running in OS X, memory allocations are no longer used since each application's memory is dynamic and changes to fit the use of the application. The "Entire Column" Rule
Although Microsoft Excel 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 is
somewhat time consuming (65,536 cells per column), Microsoft Excel does not allow you to create this kind of array in a
formula.
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 COMMAND+RETURN or CONTROL+SHIFT+RETURN to enter the formula as an
array.
The array formula results are as follows:
- 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.
- In Microsoft Excel 2001 for Mac and later, 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 nearly 600,000 cells.
In Microsoft Excel 98 Macintosh Edition, you may receive an error message that is similar to one or more of the following:Not enough memory.
Continue without Undo? In this case, the size of the worksheet array is too large for available memory, and the formula cannot be calculated. Also, because 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 A3 returns the value 0 (zero). - 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, Excel X for Mac and later versions return the result 7012242. This is the correct result.
In Microsoft Excel 2001 for Mac and earlier versions, you may receive an error message that is similar to one or more of the following:Not enough memory.
Continue without Undo?
In this case, the size of the worksheet array is too large for available
memory, and the formula cannot be calculated. Also, because 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 exceed the
maximum limits in earlier versions.
Modification Type: | Major | Last Reviewed: | 1/20/2005 |
---|
Keywords: | KB181285 |
---|
|