Return To CS 100 Home

Colour Theme   Font Size Options

Absolute Addressing

We've seen a few times now how filling can be used to help copy formulas and functions into other cells while providing an updated version. However, sometimes we don't want the cell references to actually change when we use fill. For example, if we had one cell that contained a tax value, we wouldn't want the fill to stop using that value.

Like HTML, we can use an absolute reference to point to a place in the spreadsheet and say "get the information from there." The symbol for creating an absolute reference is a $ and has three possibilities:

  1. Placing a $ in front of the column letter ($B15) will make it so that whenever you fill, the formula will never change to outside that column
  2. Placing a $ in front of the row number will make it so that the formula will never look outside that row
  3. Placing a $ in front of both the column letter and the row number ($B$15) will make it so the formula always uses that cell

We will try this out with a tax example:

  1. In Cell A15, write "tax rate", and in cell B15, write "0.05" for 5%
  2. In Cell A13, write "taxes", and in cell B13, write the formula "=B15 * B11"
    You will notice that while the answer in the first box is correct, if you try to fill you will get 0s because C15 and D15 are empty
    The formula without an absolute reference.  Since the formula relatively changed when it was filled, the nearby answers are 0
  3. To fix this, we will need to add an absolute address to the tax value in B15. Since we never want it to leave that cell, we will place a $ on both B and 15:
    $B$15 * B11
    Notice we do not put any $ in B11. This is because we still want this row to fill normally
  4. Fill it to the right again. This time, all the values will work properly

  5. The formula with an absolute reference.  Filling it now provides the correct answer