Microsoft Office Tutorials and References

In Depth Information

Figure 14-6:
Using a named array in an array formula.

To enter this formula, select seven cells in a row, type
=DayNames
, and press Ctrl+Shift+Enter.

Because commas separate the array elements, the array has a horizontal orientation. Use semicolons to create a

vertical array. Or, you can use Excel's TRANSPOSE function to insert a horizontal array into a vertical range of

cells. (See the “Transposing an array” section later in this chapter.) The following array formula, which is

entered into a seven-cell vertical range, uses the TRANSPOSE function:

{=TRANSPOSE(DayNames)}

You also can access individual elements from the array by using Excel's INDEX function. The following for-

mula, for example, returns
Wed
, the fourth item in the
DayNames
array:

=INDEX(DayNames,4)

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

turned.

Selecting an array formula range

You can select the cells that contain a multicell array formula manually by using the normal cell selection pro-

cedures. Alternatively, you can use either of the following methods:

• Activate any cell in the array formula range. Choose Home
⇒
Editing
⇒
Find & Select
⇒
Go to Special, and

then select the Current Array option. When you click OK to close the dialog box, Excel selects the array.

• Activate any cell in the array formula range and press Ctrl+/ to select the entire array.

Editing an array formula