Part II: Working with Formulas and Functions
364
Working with Array Formulas ..........................................................................................
This section deals with the mechanics of selecting cells that contain arrays and entering and editing
array formulas. These procedures differ a bit from working with ordinary ranges and formulas.Entering an array formula........................................................................................
When you enter an array formula into a cell or range, you must follow a special procedure so that
Excel knows that you want an array formula rather than a normal formula. You enter a normal for-
mula into a cell by pressing Enter. You enter an array formula into one or more cells by pressing
Ctrl+Shift+Enter.Don’t enter the curly brackets when you create an array formula; Excel inserts them for you. If the
result of an array formula consists of more than one value, you must select all the cells in the results
range before you enter the formula. If you fail to do so, only the first element of the result is returned.Selecting an array formula range .............................................................................
You can select the cells that contain a multicell array formula manually by using the normal cell
selection procedures. Or you can use either of the following methods:l (^) Activate any cell in the array formula range. Display the Go To dialog box (choose Home ➪
Editing ➪ Find & Select ➪ Go To, or just press F5). In the Go To dialog box, click the
Special button and then choose the Current Array option. Click OK to close the dialog box.
l Activate any cell in the array formula range and press Ctrl+/ to select the entire array.
Editing an array formula
If an array formula occupies multiple cells, you must edit the entire range as though it were a sin-
gle 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 16.7.FIGURE 16.7Excel’s warning message reminds you that you can’t edit just one cell of a multicell array formula.