Microsoft Office Tutorials and References

In Depth Information

**Using Cell References with a Function**

Functions

•
The colon (:) separates the top-left and bottom-right corners of a block of

cells
. You’re telling Excel: “Hey, use
this
block of cells in my formula.” For

example,
A1:A5
is a range that includes cells A1, A2, A3, A4, and A5. The range

A2:B3
is a grid that contains cells A2, A3, B2, and B3. The colon is the
range

operator
—by far the most powerful way to select multiple cells.

•
The space can find cells that are common to two or more different cell

ranges
. For example, the expression
A1:A3 A1:B10
is a range that consists of

only three cells: A1, A2, and A3 (because those three cells are the only ones

found in both ranges). The space is technically the
intersection operator
, and it’s

not used terribly often.

Tip:
As you might expect, Excel lets you specify ranges by selecting cells with your mouse, instead of

typing in the range manually. You’ll see this trick later in this chapter on page 486.

You can’t enter ranges directly into formulas that just use the simple operators. For

example, the formula
=A1:B1+5
doesn’t work, because Excel doesn’t know what to

do with the range A1:B1. (Should it sum up the range? Or average it? Excel has no

way of knowing.) Instead, you need to use ranges with functions that know how to

use them. For instance, one of Excel’s most basic functions is named SUM(); it

calculates the total for a group of cells. To use the SUM() function, you enter its name, an

open parenthesis, the cell range you want to add up, and then a closed parenthesis.

Here’s how you can use the SUM() function to add together three cells, A1, A2,

and A3:

=SUM(A1,A2,A3)

And here’s a more compact syntax that performs the same calculation using the

range operator:

=SUM(A1:A3)

A similar SUM() calculation is shown in Figure 17-5. Clearly, if you want to total a

column with hundreds of values, it’s far easier to specify the first and last cell using

the range operator rather than including each cell reference in your formula!

Figure 17-5:

Using a cell range as the

argument in the SUM()

function is a quick way to

add up a series of numbers

in a column. Note that when

you enter or edit a formula,

Excel highlights all the cells

that formula uses with

different colored borders.

In this example, you see the

range of cells C2, C3, and C4

in a blue box.