Microsoft Office Tutorials and References

In Depth Information

**Tip 29: Knowing When to Use Absolute and Mixed References**

Knowing When to Use Absolute and Mixed

References

When you create a formula that refers to another cell or range, the cell references are usually relative

references. When you copy a formula that uses relative references, the cell references adjust to their

new location in a relative manner. Assume this formula (which uses relative references) is in cell A13:

=SUM(A1:A12)

If you copy the formula to cell B13, the copied formula is

=SUM(B1:B12)

Most of the time, you want cell references to adjust when you copy formulas. That’s why most of the

time you use relative references in formulas. But some situations require either absolute or relative

references.

Using absolute references

You specify an absolute reference by using two dollar signs (one in front of the column part and one

in front of the row part). Here are two examples of formulas that use absolute references:

=$A$1

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

An
absolute
cell reference in a formula does not change, even when the formula is copied elsewhere.

For example, assume the following formula is in cell B13:

=SUM($B$1:$B$12)

When you copy this formula to a different cell, the references do not adjust. The copied formula

refers to the same cells as the original, and both formulas return the same result.

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 — and you need the copied

formula to refer to the same range as the original.