The difference between Relative and Absolute references in Excel

When you create a formula, references to cells or ranges are usually based upon their position relative to the cell that contains the formula. For example, cell B6 contains the formula =A5; Microsoft Excel finds the value one cell above and one cell to the left of B6. This is known as a relative referencing.

When you copy a formula that uses relative references, the references in the pasted formula update and refer to different cells relative to the position of the formula. For example, the formula in cell B6 has been copied to cell B7. The formula in cell B7 has changed to =A6, which refers to the cell that is one cell above and to the left of cell B7.

If you don't want references to change when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiplies cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, both references will change. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows:

=A5*$C$1

Move or copy a formula

When you move a formula, the cell references within the formula do not change. When you copy a formula, absolute cell references do not change; relative cell references will change.

1. Select the cell that contains the formula you want to move or copy.
2. Point to the border of the selection.
3. To move the cell, drag the selection to the upper-left cell of the paste area. Microsoft Excel replaces any existing data in the paste area.

To copy the cell, hold down CTRL as you drag.

Tip : You can also copy formulas into adjacent cells by using the fill handle. Select the cell that contains the formula, and then drag the fill handle over the range you want to fill.