Microsoft Office Tutorials and References
In Depth Information
If an array formula occupies multiple cells, you must edit the entire range as though it were a single cell. The
key point to remember is that you can't change just one element of an array formula. If you attempt to do so,
Excel displays the warning message shown in Figure 14-7. Click OK and press Esc to exit edit mode; then se-
lect the entire range and try again.
Figure 14-7: Excel's warning message reminds you that you can't edit just one cell of a multicell array formula.
The following rules apply to multicell array formulas. If you try to do any of these things, Excel lets you know
• You can't change the contents of any individual cell that make up an array formula.
• You can't move cells that make up part of an array formula (although you can move an entire array formula).
• You can't delete cells that form part of an array formula (although you can delete an entire array).
• You can't insert new cells into an array range. This rule includes inserting rows or columns that would add
new cells to an array range.
• You can't use multicell array formulas inside of a table that was created by choosing Insert ⇒ Tables ⇒ Table.
Similarly, you can't convert a range to a table if the range contains a multicell array formula.
To edit an array formula, select all the cells in the array range and activate the Formula bar as usual (click it or
press F2). Excel removes the brackets from the formula while you edit it. Edit the formula and then press
Ctrl+Shift+Enter to enter the changes. Excel adds the curly brackets, and all the cells in the array now reflect
your editing changes.
If you accidentally press Ctrl+Enter (instead of Ctrl+Shift+Enter) after editing an array
formula, the formula will be entered into each selected cell, but it will no longer be an
array formula. And it will probably return an incorrect result. Just reselect the cells,
press F2, and then press Ctrl+Shift+Enter.
Although you can't change any individual cell that makes up a multicell array formula, you can apply format-
ting to the entire array or to only parts of it.
Expanding or contracting a multicell array formula
Often, you may need to expand a multicell array formula (to include more cells) or contract it (to include fewer
cells). Doing so requires a few steps:
1. Select the entire range that contains the array formula.