Absolute References: Absolutely Important in Excel 2010

But if I go ahead and enter =D11+A11 in Gordon’s E11 cell, and once again copy down the E column, I’ll see this (Figure 3 – 27):

Hmmm. That doesn’t look quite right, does it? Gordon’s score surely exhibits the 5-point increment, but his colleagues seem  to have come away with nothing extra at all. What’s happened? What’s happened is this: Gordon’s bonus-conferring =D11+A11  is correctly written; it references both his test score—64, in D11—and the 5-point give-away, stashed in cell A11.

But when I copy this spot-on formula down to April’s cell in E12, her formula states: =D12+A12 and therein lies the problem.

Because relative referencing has done its thing, both row numbers in April’s formula have pumped to 12, up one from  Gordon’s 11. And even though cell E12 correctly cites Alice’s original test score, cell A12 contains…nothing. And 49 plus  nothing… is 49. And that also means that Tony’s cell bonus formula—=D13+A13—has to be wrong, too, because A13 is  likewise blank, and so on. So apart from Gordon’s original bonus calculation all the other students report the wrong bonus result, because they don’t reference the cell—A11—in which the bonus is entered. So how is this puzzlement resolved?

Like this. Return to Gordon’s cell E11—which remember, contains the correct grade bonus formula —and edit the cell to read: =D11+A$11 Then copy this revised version down the E column to all the other students. You should now be viewing the  correct, bonus-bearing grades for each student. So what’s going on? Obviously the dollar sign has something to do with it.

First, we need to understand that the dollar sign has nothing at all to do with currency formatting.

Rather, the sign is a programming convention, which freezes the part of the cell address to its immediate right. Installing the  dollar sign where we did—alongside the 11 in A11—means that no matter where we copy Gordon’s =D11+A$11, that 11 will  never change. Thus April’s formula now states: =D12+A$11 and Tony’s declares: =D13+A$11 .

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.: =$A11 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:=$A$11 in which case neither the A nor the 11 will ever change, irrespective of the  destination(s) to which they’re copied.

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 formula.

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.

To recapitulate: 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.