Microsoft Office Tutorials and References
In Depth Information
Using Cell References with a Function
• 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 here’s a more compact syntax that performs the same calculation using the
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!
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.