Excel - Relative cell references and absolute cell references

Relative cell references and absolute cell references

Cell references are used in almost all functions and they interact differently with the Auto-fill handle depending on what type of reference you have used:

The fill handle is shown when a cell is selected (by left clicking it), you will then see a green dot on the bottom right corner of the selected cell which you can click and drag in any direction  Illustrative image

There is a separate page on further use on the fill-handle found here: 

 

Relative cell references

If for instance we were looking up the SUM (total of the values within the specified range) we would use the function:

"=SUM(A1:A20)" - this is a relative cell reference. Therefore when we use the fill handle on the cell containing the function it will move the references relative to movement of the fill handle. 

i.e. If I move the fill handle right two cells the reference will change:  Illustrative image

  1. "=SUM(B2:B20)" and then
  2. "=SUM(C2:C20)" this will continue as far as I drag the fill handle.

 

Absolute cell references

Absolute cell references have limited movement when using the fill handle. This may be useful when you are calculating a value based around a single cell. For instance: Illustrative image

Imagine we were calculated VAT on lots of Items (20% of the value - UK -2017). I could calculate 20% of the grand-total but that wouldn't display for each item separately. So I could create a new column and add 20% into this so that the fill handle worked all the way down. However this takes time and will also leave you with less room on the spreadsheet.  

So imagine we have a single cell with "20%" written in *(in the percentage number format - see number formatting) when can reference our other columns to this cell and fix that cell reference in position regardless of where we move the fill handle

if a standard reference is:

A20

then the absolute reference is:

$A$20

Each "$" is fixing an axis of movement. The $ In front of A limits the movement left or right through columns, the $ in front of the 20 limits the movement up and down through rows.

This means that we can limit movement fully of a reference by placing a "$" on both a column and the row reference like shown above ($A$20) or we can limit it's movement in only one direction by either applying the "$" to the A or the 20:

$A20 (to limit the movement of the reference to up and down only) or  A$20 (to limit the movement of the reference to left to right only). However in the case fo the GIF below we don't want the reference to move at all and therefore we will leave it as $A$20

Still need help?