Microsoft Office Tutorials and References

In Depth Information

**Working with Array Formulas**

Working with Array Formulas

This section deals with the mechanics of selecting cells that contain arrays, as well as 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

Excel knows that you want an array formula rather than a normal formula. You enter a normal

formula into a cell by pressing Enter. You enter an array formula into one or more cells by

pressing Ctrl+Shift+Enter.

You can easily identify an array formula because the formula is enclosed in curly brackets in the

Formula bar. The following formula, for example, is an array formula:

{=SUM(LEN(A1:A5))}

Don’t enter the curly brackets when you create an array formula; Excel inserts them for you after

you press Ctrl+Shift+Enter. If the result of an array formula consists of more than one value, you

must select all of the cells in the results range
before
you enter the formula. If you fail to do this,

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. Alternatively, you can use either of the following methods:

h
Activate any cell in the array formula range. Choose Home

Go

To Special, and then select the Current Array option. When you click OK to close the

dialog box, Excel selects the array.

➜

Editing

➜

Find & Select

➜

h
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

single cell. The key point to remember is that you can’t change just one element of an array formula.

If you attempt to do so, Excel displays the message shown in Figure 14-6. Click OK and press Esc

to exit edit mode; then select the entire range and try again.