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
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:
- "=SUM(B2:B20)" and then
- "=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:
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