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

about it:

• 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.