Excel > Excel 2007 Foundation > Formulas

Relative and Absolute Cell References

When copying cells containing formulas, any cell references will be changed in relation to its new position.

For example, the formula =SUM (C4:C7) in cell C9 has been copied along to cells D9:H9.

Relative cell reference

As the formula was copied to the right, the cells it referred to, i.e. C4:C7, also moved right becoming D4:D7, E4:E7 and so on. This type of reference is known as a relative cell reference because the cells moved in relation to our movement.

Copying formulas across cells

This can sometimes be inconvenient. There are times when you require cells to be fixed. This type of cell reference is known as an absolute cell reference. Absolute cell references can be identified by $ symbols within the cell reference e.g. $B$5.

In the example below cell D3 contains the formula =C3*D1. When the formula is copied down to cells D4:D7 we do not wish for cell D1 to move. To fix cell D1 we will make it absolute.

Formula requiring an absolute reference

You can make a cell reference absolute in one of 2 ways:

  • Type $ symbols before the column letter and row number
  • Select the cell reference in the formula bar and press F4

Now when the formula is copied, cell C3 moves as it is a relative cell reference, whilst cell D1 remains fixed because it is an absolute cell reference.

Formula showing an absolute cell reference

There are also mixed references. Mixed references are when only the column or row is fixed. For example, $B5 is a mixed reference with the column absolute and the row relative, and B$5 has the column relative and the row as an absolute reference.

You can make a mixed reference by either typing the $ sign in the appropriate position or pressing the F4 key. The F4 key will toggle between the relative, absolute, mixed row and mixed column options.

Follow us on

Facebook  Twitter  You Tube