Excel 2019 Bible

Part II: Working with Formulas and Functions

FIGURE 18.6

Using a named array constant 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 the Excel TRANSPOSE function to

insert a horizontal array into a vertical range of cells. (See “Transposing an array” 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 the Excel INDEX function.

The following formula, for example, returns Wed, the fourth item in the DayNames array:

=INDEX(DayNames,4)

Working with Array Formulas

This section deals with the mechanics of selecting cells that contain arrays and enter-

ing 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 that Excel knows 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.

Don’t enter the curly brackets when you create an array formula; Excel inserts them for

you. If the result of an array formula consists of more than one value, you must select all

of the cells in the results range before you enter the formula. If you fail to do so, only the

first element of the result is returned.

Selecting an array formula range

You can manually select the cells that contain a multicell array formula by using the nor-

mal cell selection procedures, or you can use either of the following methods: