Chapter 18: Understanding and Using Array Formulas
18
■ (^) Activate any cell in the array formula range. Choose Home ➪ Editing ➪ Find &
Select ➪ Go To, or just press F5. The Go To dialog box appears. In the Go To dialog
box, click the Special button and then choose the Current Array option. Click OK to
close the dialog box.
■ (^) Activate any cell in the array formula range, and press Ctrl+/ (forward slash)
to select the cells that make up the array.
Editing an array formula
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 a multicell array formula. If you attempt to do so, Excel displays the message shown
in Figure 18.7.
FIGURE 18.7
Excel’s warning message reminds you that you can’t edit just one cell of a multicell array for-
mula.
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 curly brackets from the formula while you
edit it. Edit the formula and then press Ctrl+Shift+Enter to enter the changes. All of the
cells in the array now reflect your editing changes (and the curly brackets reappear).
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 makes up an array
formula.
■ (^) You can’t move cells that make up part of an array formula (but you can move an
entire array formula).
■ (^) You can’t delete cells that form part of an array formula (but 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 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.