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
Use the New Name dialog box to create a formula named MonthNames that consists of the
following formula definition:
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
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:
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