Microsoft Office Tutorials and References
In Depth Information
More of the Same
and so on. Now every student formula reads correctly, because each refers to the same cell containing
the grade bonus—A11.
This exercise exemplifies what’s called absolute cell referencing , a spreadsheet option in which part
of a cell address is held constant , for the kinds of reasons we’ve just described. It’s also certainly possible
to place that dollar sign before a column letter , too, if you need to, e.g.:
Here the A, or column-referencing segment of the cell address, will never change when it’s copied.
And if you need to, you can also type:
in which case neither the A nor the 11 will ever change, irrespective of the destination(s) to which they’re
Here, then, we’ve witnessed the potential downside of relative cell referencing. Precisely because
relative referencing shifts cell addresses according to their distance from the original, source cell, a series
of errant references has crept into our grading process, distorting all our grades save the original, source
And if all these relatives and absolutes are leaving you feeling slightly groggy, you’re not alone. This
topic is also an acquired taste, and in the early going it takes some doing to acquire it. But give it some
thought, play around with it with some mock formulas, and your taste buds should acclimate. With
practice they should become second nature to you.
You use relative referencing when the same kind of formula needs to be copied down (or across)
similar rows or columns of data—such as our grade book example. But of course, the copied formulas
can’t be identical , because each one needs to calculate a different set of cell references—e.g., Gordon’s
grades on row 11, April’s on row 12, etc.
You’ll want—or need—to use an absolute cell reference when different formulas need to reference
the same cell repeatedly, e.g., our grade bonus example, where each student’s grade adds the point
bonus stored in A11.
More of the Same
And what about all those other functions? Excel has hundreds of them; and while you’ll be pleased to
learn that we don’t have room to expound them all, it may be time to recall that bit of unasked-for advice
I issued to you about 30 pages ago: namely that it really pays to learn about as many functions as you
When I first encountered spreadsheets—in the Paleolithic late 80s, pioneer days when Lotus 1-2-3
ruled the roost and the Undo button was merely a gleam in Bill Gates’ eye—my then-boss handed me a
rather copious 1-2-3 manual, and wrapped it with one laconic instruction: Learn it. And when I came
upon the chapter describing functions—and many of the ones we still use date back to that time—I was
incredulous that anyone could actually find a place for these arcane concoctions. But as I learned more
about spreadsheets I came to see the wisdom—and the potential value—of a good many of them. In fact,
we already know five functions; let’s learn some more. Not all of them, mind you, but some important
ones—after we learn a few preliminaries.
First, you’ll want to know that all the Excel functions are neatly catalogued and warehoused inside
the buttons shelved in the Function Library group in the Formulas tab (Figure 3 - 28):