Referencing in Excel, Image Credit S Brooks 2016Excel is a spreadsheet application that is primarily used for calculations. When you create a formula you use the cell reference in order to calculate rather than the actual number. This is so that if we need to change some of the numbers we do not have to recreate the formula for the result. You probably will need the same formula in several cells in your spreadsheet. So understanding how referencing works is vital to making your experience in Excel a good one and creating a spreadsheet quicker.

Here we have an example of a small spreadsheet where we need to create formulae to add up columns and rows. Referencing 1

Think of creating your formula as a treasure hunt type of navigation. North four steps then South/ West 6 steps and so on to get to the treasure waiting there which is the total.

So to add up the first column select the cell B6 and type in the = sign. This anchors your formula to this cell where you indeed want the answer to be.

Select the first cell in the spreadsheet which is B2 in my case, type in the + sign and select the next cell needed which is B3, type in a + sign and select the next cell B4, type in the + sign and select the cell B5. This is the last cell in the area we want to add together so press the Enter key to finish the formula.

You have just created a relative referencing formula.

Relative to where you start (the cell B6) move straight up in the same column 4 cells. This gets you to cell C2. Plus, relative to where you are (still in B6) move up in the same column 3 cells to B3. Plus relative to where you are (B6) move up 2 cells to B4, plus relative to where you are (B6) move up 1 cell to B5. ref2

When you copy a cell that has a formula in it and then you paste it using the straight forward paste Excel pastes the formula not the result from your copy action. If you have moved to the right by one column then the formula is re written for you to reflect that as now you are in a new column C so relative to where you start (C6) move up 4 cells to C2, plus move up 3 cells to C3, plus move up 2 cells to C4, plus move up 1 cell to C5. And so you have your new answer in the cell C6 by copy and pasting the formula from B6. You can select the cell B6 or C6 and copy and paste into D6 and you will have the correct answer as all the formulae are using relative referencing.

What about Absolute Referencing

I am sure that some of you have heard the word ‘absolute referencing’ but not really understood its meaning or potential. You may however have come across the $ sign inside your formulae and taken a sharp intake of breath as the formula now looks alien and difficult. Not so! Be calm, I shall explain.

There are four states to absolute referencing, and here is an example

B2   –   simple relative reference

$B$2   –   absolute reference

B$2   –   combination relative and absolute reference

$B2   –   combination relative and absolute reference

What the $ sign does is it tells you that you are keeping the column or the row or indeed both as a constant. So if you copy a formula with a $ sign in it, whatever the $ sign is in-front of will still be the same


Please enter your comment!
Please enter your name here