Microsoft Office Tutorials and References

In Depth Information

You can also display the entire 12-item array, but it requires 12 adjacent cells to do so. For example, to enter the

12 items of the array into A3:L3, follow these steps (which assume that you used the New Name dialog box to

create the formula named
MonthNames
):

1.
Select the range A3:L3.

2.
Type
=MonthNames
in the Formula bar.

3.
Press Ctrl+Shift+Enter.

Pressing Ctrl+Shift+Enter tells Excel to insert an array formula into the selected cells. In this case, the single

formula is entered into 12 adjacent cells in Figure 3-24. Excel places brackets around an array formula to re-

mind you that it's a special type of formula. If you examine any cell in A3:L3, you'll see its formula listed as

follows:

{=MonthNames}

Figure 3-24:
You can enter a named formula that contains a 12-item array into 12 adjacent cells.

You'll notice that you can't delete any of the months because the 12 cells make up a multicell array formula — a

single formula that occupies multiple cells.

To insert the month names into A1:A12 (a vertical range):

1.
Select the range A1:A12.

2.
Type
=TRANSPOSE(MonthNames)
in the Formula bar.

3.
Press Ctrl+Shift+Enter.

Creating a dynamic named formula

A
dynamic
named formula is a named formula that refers to a range not fixed in size. You may find this concept

difficult to grasp, so a quick example is in order.

Examine the worksheet shown in Figure 3-25. This sheet contains a listing of sales by month, through the

month of June.