Chapter 16: Introducing Array Formulas
365
The following rules apply to multicell array formulas. If you try to do any of these things, Excel lets
you know about it:l (^) You can’t change the contents of any individual cell that makes up an array formula.
l You can’t move cells that make up part of an array formula (but you can move an entire
array formula).
l You can’t delete cells that form part of an array formula (but you can delete an entire array).
l (^) 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.
l (^) 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 for-
mula and then press Ctrl+Shift+Enter to enter the changes. All the cells in the array now reflect
your editing changes.
Caution
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. n
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:- Select the entire range that contains the array formula.
- Press F2 to enter Edit mode.
- Press Ctrl+Enter. This step enters an identical (non-array) formula into each selected cell.
- Change your range selection to include additional or fewer cells, but make sure the
active cell is in a cell that’s part of the original array. - Press F2 to re-enter Edit mode.
- Press Ctrl+Shift+Enter.