Page 177 - Excel Progamming Weekend Crash Course

k540629 ch12.qxd 9/2/03 9:34 AM Page 152

152 Saturday Afternoon

Relative Cell References

A relative cell reference consists of the cell’s column letter and row number. For example, A3
is a relative reference. In this case, relative means the reference does not always refer to the
specific cell A3. Here’s how this works.
Suppose you enter the formula =A3+A4 into cell A5. This means add the values in cells
A3 and A4. So far, there doesn’t seem to be anything relative about these references, but to
Excel, what it really means is add the values in the two cells immediately above this cell.
This becomes evident only when you copy the formula to another cell. Suppose you copy it
to cell B5. Excel changes the formula to =B3+B4. Note that it keeps the same relative mean-
ing: add the values in the two cells immediately above this cell. No matter where you copy
this formula, it changes so it always adds the two cells above the cell containing the for-
mula.
Relative cell references are the default in Excel and with good reason. Relative references
make it possible to create a formula and then copy it to different worksheet locations. For
example, if you have 20 columns of numbers to add, you can create the formula for the first
column and place it in the cell below the column; then, copy the formula to the cells below
the other columns, and the references automatically adjust so that each copied formula
sums the numbers in the column above it.
All Excel formulas begin with an equal sign. This tells Excel that it is a for-
mula and not text data.
Note

Absolute Cell References

Just the opposite of the relative cell reference, an absolute cell reference does not change
when it is copied. The reference always refers to the same worksheet cell. You create an
absolute cell reference by including dollar signs in the reference: $A$2. Here’s a formula
that uses absolute references:
=$A$3+$A$4

If you copy this formula to another worksheet location, the reference does not change.
The formula always adds the values in cells A3 and A4.
A cell reference can also be part absolute and part relative with the absolute part being
either the column letter or the row number. For example:

$A3 — Column absolute, row relative
A$3 — Row absolute, column relative
When a formula containing such a reference is copied, the relative part is adjusted to
maintain its original relative meaning, while the absolute part remains unchanged. Table
12-1 gives some examples.