Excel Basics - Using the $ Sign for Relative and Absolute References (or "Sticking" to Cells)

When using Excel,  you often write a formula in a cell that refers to another cell. Here's a simple example:

It's pretty easy to understand:

• There is a value in column A, row 1 and that is cell A1
• There is a value in column B, row 1 and that is cell B1
• There is a formula in column C, row 1 (cell C1) which is "=A1+B1" which means "add the value in cell A1 to the value in cell B1".

When I click away from cell C1, the sum of cells A1 and B1 is shown.  Easy!

Say I want to add up more rows with values on columns A and B.  First I enter all the values I want to add:

Then I just copy the formula from cell C1 to cells C2 through to C9.  This gives me:

What you can see above is that when the formula is copied to each row, Excel keeps the column letters the same (so A and B) but changes the row numbers.  So in the example above on row 9, it has copied the formula from cell C1 (which was "=A1+B1") and assumed you want to add up the values in row 9 and has made the formula ("=A9+B9").  

This is an example of Excel using relative references, i.e. it updates the formula relative to the row you copy the formula to.  Note, if you copy a formula across different columns of the same row then Excel will modify the column letter part of the formula.

"This is obvious!" I hear you cry.  But what if you want to copy a formula down rows (or across columns) and not have the row/column change.  Here's where absolute references come in!!

Imagine you're on holiday abroad using a local currency and you want to keep track of how much you're spending in your home currency.  You can type the exchange rate in one cell on the spreadsheet and use an absolute reference to use the exchange rate in every calculation.  Here's an example:

Cell A2 has the exchange rate.  So every 1 unit of local currency equates to 2 units of home currency.  I've logged all my local currency spends and in cell D5 have entered the formula to convert to home currency by multiplying by the exchange rate, (the * sign means multiply).

What can seen is that the formula is "=C5*$A$2".  So what do we have here?

• The C5 is a relative reference.  When we copy the formula down to rows 6 through to 9, C5 becomes C6, becomes C7 and so on.
• The $A$2 is an absolute reference.  When we copy the formula down to rows 6 through to 9, the $ signs means "keep the reference to cell A2".  We're effectively "sticking" the formula to cell A2.  Here we can see it in action:

As you can see the formula in cell D9 is "=C9*$A$2"; we've kept the link to cell A2, as can be seen in the calculation results in the cells above.

We can use these absolute and relative references in other ways.  Here's some examples:

• =$A$1:$C$5 would be used in a formula that works across a number of rows and columns (so columns A to C and rows 1 to 5 in the example).  As you copy the formula into other cells on the spreadsheet it always refers to this range.
• =$A1 is a mixed reference, the A is absolute, the 1 is relative.  This would mean that if you copy the formula across and down rows, the link to column A would remain but the link to row 1 would change.
• You can also do the opposite of the above with something like =A$1, another mixed reference but one that anchors to row 1 and allows the column to change.

Hope that was useful!