Microsoft Office Tutorials and References

In Depth Information

If you want a cell reference to remain constant when the formula using it is copied to

another cell, you can use an absolute reference. To write a cell reference as an absolute

reference, type
$
before the row letter and the column number. For example, if you want

the formula in cell D16 to show the sum of values in cells C10 through C14 regardless of

the cell into which it is pasted, you can write the formula as
=SUM($C$10:$C$14)
.

Tip
Another way to ensure your cell references don’t change when you copy the formula

to another cell is to click the cell that contains the formula, copy the formula’s text in the

formula bar, press the Esc key to exit cut-and-copy mode, click the cell where you want to

paste the formula, and press Ctrl+V. Excel doesn’t change the cell references when you copy

your formula to another cell in this manner.

One quick way to change a cell reference from relative to absolute is to select the cell

reference in the formula box and then press F4. Pressing F4 cycles a cell reference

through the four possible types of references:

●
Relative columns and rows (for example, C4)

●
Absolute columns and rows (for example, $C$4)

●
Relative columns and absolute rows (for example, C$4)

●
Absolute columns and relative rows (for example, $C4)