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