Excel 2019 Bible

Chapter 18: Understanding and Using Array Formulas

18

Using Multicell Array Formulas

This section contains examples that demonstrate additional features of multicell array

formulas (array formulas that are entered into a range of cells). These features include cre-

ating arrays from values, performing operations, using functions, transposing arrays, and

generating consecutive integers.

Creating an array from values in a range

The following array formula creates an array from a range of cells. Figure 18.8 shows a

workbook with some data entered into A1:C4. The range D8:F11 contains a single array

formula:

{=A1:C4}

FIGURE 18.8

Creating an array from a range

The array in D8:F11 is linked to the range A1:C4. Change any value in A1:C4, and the corre-

sponding cell in D8:F11 reflects that change. It’s a one-way link, of course. You can’t change

a value in D8:F11.

Creating an array constant from values in a range

In the preceding example, the array formula in D8:F11 essentially created a link to the

cells in A1:C4. It’s possible to sever this link and create an array constant made up of the

values in A1:C4:

1. Select the cells that contain the array formula (the range D8:F11, in this
   example).


2. Press F2 to edit the array formula.