Article ID: 166342 - View products that this article applies to. This article was previously published under Q166342 ## On This Page## SUMMARYIn the versions of Microsoft Excel that are listed in the "Applies to" section, the "Calculation Specifications" Help topic lists
the limitations for working with an array. This article describes the
limitations of arrays in Excel. ## MORE INFORMATION In Excel, arrays in worksheets are limited by
available random access memory, by the total number of array formulas, and by the
"entire column" rule. ## Available memoryThe Excel versions that are listed in the "Applies to" section do not impose a limit on the size of worksheet arrays. Instead, you are limited only by the available memory on your computer. Therefore, you can create very large arrays that contain hundreds of thousands of cells.## The "entire column" ruleAlthough you can create very large arrays in 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 is time consuming, Excel does not allow you to create this kind of array in a formula.Note There are
65,536 cells in a column in Microsoft Office Excel 2003 and in earlier versions of Excel. There are 1,048,576 cells in a column in Microsoft Office Excel 2007.## Maximum array formulasIn Excel 2003 and in earlier versions of Excel, 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 items: - 65,472 array formulas that refer to Sheet2
- 65,472 array formulas that refer to Sheet3
- 65,472 array formulas that refer to Sheet4
## Array formula examplesThe following is a list of array formula examples. To use these examples, create a new workbook, and then 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.## Excel 2007- A1: =SUM(IF(B1:B1048576=0,1,0)) The formula in cell A1 returns the result 1048576. This result is correct.
- A2: =SUM(IF(B:B=0,1,0)) The formula in cell A2 returns the result 1048576. This result is correct.
- A3: =SUM(IF(B1:J1048576=0,1,0)) The formula in cell A3 returns the result 9437184. This result is correct.
**Note**The formula may take a long time to calculate the result because the formula is checking more than 1 million cells. - A4: =SUM(IF(B:J=0,1,0)) The formula in cell A4 returns the result 9437184. This result is correct.
**Note**The formula may take a long time to calculate the result because the formula is checking more than 1 million cells. - A5: =SUM(IF(B1:DD1048576=0,1,0)) When you enter this formula in cell A5, you may receive one of the following error messages:To determine the unique number that is associated with the message that you receive, press CTRL+SHIFT+I. The following number appears in the lower-right corner of this message:Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.In this case, the size of the worksheet array is too large for the available memory. Therefore, the formula cannot be calculated.101758
Additionally, Excel may appear to stop responding for a few minutes. This is because the other formulas that you entered must recalculate their results. After the results are recalculated, Excel responds as expected. The formula in cell A5 returns the value 0 (zero).
## Excel 2003 and earlier versions of Excel- 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?In this case, the size of the worksheet array is too large for the available memory. Therefore, the formula cannot be calculated.Not enough memory.
Additionally, Excel may appear to stop responding for a few minutes. This is because the other formulas that you entered must recalculate their results. After the results are recalculated, Excel responds as expected. The formula in cell A5 returns the value 0 (zero).
- LINEST()
- MDETERM()
- MINVERSE()
- MMULT()
- SUM(IF())
- SUMPRODUCT()
- TRANSPOSE()
- TREND()
Note The following facts about the functions are helpful to remember.- 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.
## Properties |