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

Search JabSto ::

Custom Search