The most important concept that you must master in Excel is Relative and Absolute Addressing.  This is the basic foundation of Excel.  Without understanding this, you will not work with excel with ease.

Formula Philosophy:

  • Excel is defaulted to Relative Addressing!
  • Relative Addressing is when you copy a formula across and it increments the letters in the formula.
  • Relative Addressing is when you copy a formula down and it increments the numbers in the formula.
  • The formula does change but it does not have to do what column or row you are sitting at.  It is irrelevant.
  • Excel’s job is to zoom into the formula based on how you are copying/filling (across or down), it will do exactly what is told to do.  Letters across and numbers down.
    For example: =Sum(F20:F40) Copying across will increment the letter “F” to “G”.
    For example: =Sum (B2:F2) Coping down will increment the row “2” to “3”.
  • Absolute Addressing is opposite of Relative Addressing.  You want to lock in the cell in a formula so that when you copy across or down the particular cell does not change.  It is absolutely locked.  Place the Insert beam in the formula before, in the middle or after the cell that needs to be locked.  Hit F4 key. This will place a $ signs next to the letter and row number.  For example , if you need the value in G2 and it must be used for all the rows, then you must lock it.  Excel does not tell you that it has to be locked.  For example, G2 is the cell I want to use, it should look like this: $G$2.
  • F4 has four different absolute set up.
    • Hitting F4 the 1st time Excel places both dollar signs – $G$2
    • Hitting F4 a 2nd times Excel places a dollar sign in front of the number – G$2
    • Hitting F4 a 3rd time Excel places a dollar sign in front of the letter – $G2
    • Hitting F4 a 4th time Excel keeps the original letter and number – G2
  • Why would I want to use Absolute?  The reason is that if you have a bonus of 15% in a cell and the salaries are going to be multiplied by 15%, then I will write a formula that uses the salaries times the bonus rate that is located in the cell.  I would not type the following formulas:
    • =B2*15%
    • =B3*15%
    • =B4*15% and so on.
      This does not work because if you decide to change the 15% to 20% then you have to retype the formula and copy it down. If the formula is very large, you may make other typing mistakes.
    • Remember, Excel works for you!

Here is an example of an Excel sheet with name, salary, bonus amount, and total salary.  15% is in G2

    • Place 15% percent in the cell G2.  Then type in C3:
    • =B2*G2 and while the insert beam is blinking, hit F4 the formula now become
      =B2*$G$2 hit Enter
    • Copy the formula and the rest is typed in for you leaving G2 intact.
    • =B3*$G$2
      =B4*$G$2

For your information, if you did not place the absolute in G2, when you copy down, Excel zooms into the formula and it is told to increment the row numbers.  Therefore, B3 becomes B4 which then becomes B5 and G2 will become G3 which then becomes G4.  The last 2 formulas will produce a zero values become something times nothing gives you ZERO!

Pressing Ctrl+` keys (` is located to the left of the number 1 key) shows you the formulas.  Pressing those two keys will return back to normal view. It is an On/Off switch.

Practice makes perfect and Absolute is used everywhere in Excel.  You can not hide from this concept!

**Click on the example to see it better.

Advertisements