Excel 2010 Bible

Chapter 16: Introducing Array Formulas

357

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 actually

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 C2:C7.

This array formula returns exactly the same values as these six normal formulas entered into indi-

vidual cells in D2:D7:

=B2*C2

=B3*C3

=B4*C4

=B5*C5

=B6*C6

=B7*C7

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

l (^) It’s a good way to ensure that all formulas in a range are identical.
l Using a multicell array formula makes it less likely that you’ll overwrite a formula acciden-
tally. You can’t change one cell in a multicell array formula. Excel displays an error mes-
sage if you attempt to do so.
l (^) Using a multicell array formula will almost certainly prevent novices from tampering with
your formulas.
Using a multicell array formula as described in the preceding list also has some potential
disadvantages:
l (^) It’s impossible to insert a new row into the range. 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.
l If you add new data to the bottom of the range, you need to modify the array formula 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 16.2, which is similar

to Figure 16.1. Notice, however, that the formulas in column D have been deleted. The goal is to

calculate the sum of the total product sales without using the individual calculations that were in

column D.