Excel 2019 Bible

Chapter 18: Understanding and Using Array Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . 

18

You can’t insert a multicell array formula into a range that has been designated a table (by choosing Insert ➪ Tables
➪ Table). In addition, you can’t convert a range that contains a multicell array formula to a table.

The formula is entered into all six selected cells. If you examine the Formula bar, you see

the following:

{=B2:B7*C2:C7}

Excel places curly brackets around the formula to indicate that it’s an array formula.

This formula performs its calculations and returns a six-item array. The array formula actu-

ally works with two other arrays, both of which happen to be stored in ranges. The values

for the first array are stored in B2:B7, and the values for the second array are stored in

C 2:C 7.

This multicell array formula returns the same values as these six normal formulas entered

into individual cells in D2:D7:

=B2*C2

=B3*C3

=B4*C4

=B5*C5

=B6*C6

=B7*C7

Using a multicell array formula rather than individual formulas does offer a few advantages:

■ (^) It’s a good way to ensure that all formulas in a range are identical.
■ (^) Using a multicell array formula makes it less likely that you’ll overwrite a formula
accidentally. You can’t change or delete just one cell in a multicell array formula.
Excel displays an error message if you attempt to do so.
■ (^) Using a multicell array formula will almost certainly prevent novices from tamper-
ing with your formulas.
Using a multicell array formula as described in the preceding list also has some potential
disadvantages:
■ (^) Inserting a new row into the range is impossible. But in some cases, the inability
to insert a row is a positive feature. For example, you might not want users to add
rows because it would affect other parts of the worksheet.
■ (^) If you add new data to the bottom of the range, you need to modify the array for-
mula to accommodate the new data.
A single-cell array formula
Now it’s time to take a look at a single-cell array formula. Check out Figure 18.2, which is
similar to Figure 18.1. Notice, however, that the formulas in column D have been deleted.