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