Description of the limitations for working with arrays in Excel 2000, Excel 2002, and Excel 2003 (166342)

MORE INFORMATION

In Microsoft Excel, arrays in worksheets are limited by available random access memory, the total number of array formulas and by the "entire column" rule.

Available Memory

The Microsoft Excel versions that are listed at the beginning of this article do not impose a limit on the size of worksheet arrays; you are limited only by the 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 you can create very large arrays in Microsoft Excel, you cannot create an array that uses a whole column or multiple columns of cells. Because recalculating an array formula that uses a whole column of cells (there 65,536 cells in a column) is a little time consuming, Microsoft Excel does not allow you to create this kind of array in a formula.

Maximum Array Formulas

In the Microsoft Excel versions that are listed at the beginning of this article, a single worksheet may contain a maximum of 65,472 array formulas that refer to another worksheet. If you want to use more formulas, split the data into multiple worksheets so that there are fewer than 65,472 references to a single worksheet. For example, in Sheet1 of a workbook, you can create the following:

• 65,472 array formulas that refer to Sheet2
• 65,472 array formulas that refer to Sheet3
• 65,472 array formulas that refer to Sheet4

If you try to create more than 65,472 array formulas that refer to a specific worksheet, the array formulas that you enter after array formula number 65,472 may disappear when you enter them.

Array Formula Examples

The following is a list of array formula examples. To use these examples, create a new workbook, and enter each formula as an array formula. To do so, type the formula in the formula bar, and then press CTRL+SHIFT+ENTER to enter the formula.

• A1: =SUM(IF(B1:B65535=0,1,0))

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

• A2: =SUM(IF(B:B=0,1,0))

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

• A3: =SUM(IF(B1:J65535=0,1,0))

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

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

• A4: =SUM(IF(B:J=0,1,0))

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

• A5: =SUM(IF(B1:DD65535=0,1,0))

  When you enter the formula in cell A5, you may receive one of 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 the 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 responds as expected. 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. The following is a list of some of the functions in Microsoft Excel that use arrays:

• LINEST()
• MDETERM()
• MINVERSE()
• MMULT()
• SUM(IF())
• SUMPRODUCT()
• TRANSPOSE()
• TREND()

Note The following are helpful facts to remember about the functions.

• If any cells in an array are empty or contain text, MINVERSE returns the #VALUE! error value.
• MINVERSE also returns the #VALUE! error value if the array does not have an equal number of rows and columns.
• MINVERSE returns the #VALUE! error if the returned array exceeds 52 columns by 52 rows.
• The MMULT function returns #VALUE! if the output exceeds 5460 cells.
• The MDETERM function returns #VALUE! if the returned array is larger than 73 rows by 73 columns.

For more information about specifications and limitations, click Microsoft Excel Help on the Help menu, type Excel specifications and limits in the Office Assistant or the Answer Wizard, and then click Search to view the topic.