Microsoft Office Tutorials and References

In Depth Information

**Knowing When to Use Absolute References**

Knowing When to Use Absolute References

When you create a formula that refers to another cell or range, the cell or range reference can be

relative or absolute. A
relative
cell reference adjusts to its new location when the formula is

copied and pasted. An
absolute
cell reference does not change, even when the formula is copied and

pasted elsewhere. An absolute reference is specified with two dollar signs; for example:

=$A$1

=SUM($A$1:$F$24)

A relative reference, on the other hand, does not use dollar signs:

=A1

=SUM(A1:F24)

The majority of cell and range references you will ever use are relative references. In fact, Excel

creates relative cell references in formulas except when the formula includes cells in different

worksheets or workbooks. When do you use an absolute reference? The answer is simple: The

only time you even need to
think
about using an absolute reference is if you plan to copy the

formula.

The easiest way to understand this concept is with an example. Figure 69-1 shows a simple

worksheet. The formula in cell D2, which multiplies the quantity by the unit price, is

=B2*C2

Figure 69-1:
Copying a formula that contains relative references.

This formula uses relative cell references. Therefore, when the formula is copied to the other cells

in the column, the references adjust in a relative manner. For example, copy the formula to cell

D3, and it becomes

=B3*C3