Relative Cell References in Excel Formulas

When you enter a formula into Excel, it recognises it as a pattern as opposed to numbers.

So for example, this simple addition formula is asking Excel to remember to add up the 4th cell to the left (B6) , the 3rd cell to the left -(C6), 2nd to the left (D6) and the 1st to the left (E6) relative to the cell the formula is in (F6).

The great thing about this is that if you want the same formula anywhere else in your sheet you can copy and paste it.

Wherever you copy and paste it to the formula will add together the fourth, third, second and first cells to the left of the cell that the formula is now in. So if you copy the formula to F12 it will add up F8, F9, F10 and F11.

Really understanding how Excel manages referencing and data is vital as you learn to use it’s more powerful formulas and functions. These more power functions are covered in our more advanced Excel courses.

relative 1

You can copy and paste formulas or just drag them (grab bottom right hand corner of call and drag) into the cells you want them in. The cells the formula refers to will change to match where you have copied it to. See below, formula copied from F6 and pasted into F7. It now refers to cells in row 7.

relative 2

 

Want to learn about data linking? Follow this article to find out more.

Want to learn more about Font formatting? Follow this article to find out more.

To try this out and learn more like this, attend our Excel introduction training course in London or Guildford.