Microsoft Office Tutorials and References

In Depth Information

**Selecting the Range You Need**

Figure 4–5.
Same range, different result: Excel adds the cells containing values, until it encounters a cell that’s

either blank or contains text.

What Excel does here is incorporate each cell directly above H8 into its formula, until it

lands upon a cell that’s vacant—in this case, H5. Thus, Excel here would plan to add

only the values in H6 and H7.

This is interesting, but it presents us with a problem—what do we do if we still want to

add all the values in the range H3:H7?

Selecting the Range You Need

To remedy this, instead of pressing Enter as in the previous example—which produced

the result 267—this time click and drag H3 through H7, as shown in Figure 4–6. Now the

expression in H8 should read as follows

=SUM(H3:H7)

, just as it did in our original case.

Then press Enter.

Figure 4–6.
Selecting the entire range you want

What you should learn from this second case—the case of the missing value in cell H5—

is that the user can always override Excel’s decision about which cells are going to be

added with

SUM

. When you click AutoSum, Excel first displays the range it’s about to add,

as per the screenshots; and if you approve, just press Enter. But if you want to add the

cells in a different range—which can be anywhere in the worksheet—not just in the

same column—then click and drag across that range, and only then press Enter (see

Figure 4–7).