Microsoft Office Tutorials and References

In Depth Information

**Advanced Techniques That Use Names**

Using arrays in named formulas

An array is a collection of items. You can visualize an array as a single-column vertical collection,

a single-row horizontal collection, or a multirow and multicolumn collection.

Part IV of this topic discusses arrays and array formulas, but this topic is also relevant

when discussing names.

You specify an array by using brackets. A comma or semicolon separates each item in the array.

Use a comma to separate items arranged horizontally and use a semicolon to separate items

arranged vertically.

Use the New Name dialog box to create a formula named
MonthNames
that consists of the

following formula definition:

={“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”}

This formula defines a 12-item array of text strings, arranged horizontally.

When you type this formula, make sure that you include the brackets. Entering an array

formula into the New Name dialog box is different from entering an array formula into

a cell.

After you define the
MonthNames
formula, you can use it in a formula. However, your formula

needs to specify which array item to use. The INDEX function is perfect for this. For example, the

following formula returns
Aug:

=INDEX(MonthNames,8)

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 A1:L1, follow these steps:

1.
Use the New Name dialog box to create the formula named
MonthNames.

2.
Select the range A1:L1.

3.
Type
=MonthNames
in the Formula bar.

4.
Press Ctrl+Shift+Enter.

Using 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-23. Excel places brackets around an

array formula to remind you that it’s a special type of formula. If you examine any cell in A1:L1,

you’ll see its formula listed as

{=MonthNames}