Microsoft Office Tutorials and References

In Depth Information

**Working with Array Formulas**

Figure 14-6:
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:

h
You can’t change the contents of any individual cell that make up an array formula.

h
You can’t move cells that make up part of an array formula (although you can move an

entire array formula).

h
You can’t delete cells that form part of an array formula (although you can delete an

entire array).

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

h
You can’t use multicell array formulas inside of a table that was created by choosing

Insert

Table. Similarly, you can’t convert a range to a table if the range contains

a multicell array formula.

Tables

➜

➜

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

You can use Ctrl+/ to automatically select the cells in an array that includes the active cell.

2.
Press F2 to enter edit mode.