Microsoft Office Tutorials and References

In Depth Information

**Naming Array Constants**

Figure 14-4:
Creating a named array constant.

Notice that in the New Name dialog box, the array is defined by using a leading equal sign (=).

Without this equal sign, the array is interpreted as a text string rather than an array. Also, you

must type the curly brackets when defining a named array constant; Excel does not enter them

for you.

After creating this named array, you can use it in a formula. Figure 14-5 shows a worksheet that

contains a single array formula entered into the range A1:G1. The formula is

{=DayNames}

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

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 formula, for example, returns
Wed
, the fourth item in the
DayNames
array:

=INDEX(DayNames,4)