Microsoft Office Tutorials and References

In Depth Information

**Create an Array Formula**

Chapter 4: Getting More Out of Formulas

3
Type your formula.

●

In the places where you

would normally enter a

cell reference, you can

type a range reference

that includes the cells

you want to use.

4
When the formula is

complete, press

Ctrl+Shift+Enter.

3

Excel enters the array

formula into all the

selected cells.

●

The formula is

surrounded by braces

({ and }). This identifies

the formula as an array

formula.

Note:
When you enter array

formulas, you never need to enter

these braces yourself; Excel adds

them automatically.

●

Did You Know?

In an array formula, Excel sets up a

correspondence between the array cells and

the cells of the range used in the formula. In

this section’s example, the array consists of

cells C13 through F13, and the range used

in the formula consists of cells C11 through

F11. Excel sets up a correspondence

between array cell C13 and input cell C11,

between D13 and D11, and so on. To

calculate the value of cell C13, for example,

Excel takes the value from cell C11 and

substitutes that in the formula.

Change It!

Excel treats arrays as a unit, so you

cannot move or delete part of an array.

If you need to work with an array, you

must select the entire range. Note that

you can select an array quickly by

selecting one of its cells and pressing

Ctrl+/. If you want to reduce the size of

an array, select it, click inside the

formula bar, and then press Ctrl+Enter

to change the entry to a normal formula.

You can then select the smaller range

and re-enter the array formula.