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
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:
If you copy the formula to cell B13, the copied formula is
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
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:
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:
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.