Absolute References

Download starting Sheet

Now we want to enter a formula to calculate how many days ago we spent the money. 

Steps:

1. In cell J1, enter the label: Today
2. In cell K1, enter the formula:  =TODAY()
3. In cell F1, enter the formula:  =K$1-A2
4. Select F1 and press CTRL + SHIFT + #  (to format as a number)
5. Click on the small square at the bottom right of the cell to copy the formula down

NOTE:

• In the formula =K$1-A2   we use "$" before the 1.
• What does this mean ?
  • Unfortunately for 1, it doesn't mean that it's suddenly very rich.
  • ... what it means is that when we copy the formula down, the reference to the cell K1 always remains the same, whereas the reference to A2 increases with the row that the formula is in.
• If we were to use =$K1-A2  it would mean that copying the formula to the right or left would leave the reference to K1 unchanged.
• .. and If we use =$K$1-A2  it means that no matter where we copy the formula to, the reference to K1 will remain unchanged.
• In summary:  The $ fixes the reference.

Practise entering the formula with, and without the absolute references in order to get a feel for how it works.

Absolute references are very important as they encourage us to reuse calculations thus keeping our sheet efficient, and uncomplicated. We could have used =TODAY()-A2   but that would cause the spreadsheet to become slower when the rows increase.

Your sheet should now look like this

Next Lesson: Teach Yourself Excel Lesson 17 - Copy And Paste Special Values

Training Video on Absolute References in Excel: