Home » Excel » Working with Absolutes

Working with Absolutes

Navigate

An Absolute is a type of reference you can make in Excel. At it’s simplest form, a Reference can be as easy as =A2, where we are referencing whatever value A2 may hold and making it our current cell’s value.  Formulas we have covered so far such as SUMS and VLOOKUPS are examples of references.

The below SUM formula references Cells E3, E4, E6, E7, and E9
=SUM(E3,E4,E6,E7,E9)

The below VLOOKUP formula references Cell A2, and Columns J through M.
=VLOOKUP(A2,J:M,4,0)

There are three types of references in Excel, Relative, Absolute, and Mixed. If you have been following the prior tutorials, you have been working with Relative references, Excel’s default reference type.

Absolute references require a $ to be put before either the Column reference, Row reference, or both.  When you write a formula and drag it either up or down, the referenced cells and columns in that formula will move along with it. An Absolute reference will stop that movement.

For example, I have a formula that says =A2.
If I extend this formula to the right once, the new formula will be =B2.
If I extend this formula down once, the new formula will be =A3.
Making my original formula =$A$2 causes both movements to stay as 5
SumAbsolute2


Absolute Example

An Absolute reference can be made by placing a dollar sign $ in your formula, or by pressing F4. Let’s go over how this works and how it changes the SUM formula we have used so far.

We will be using the below screenshot for reference in this example.

  1. We have the total number of hours billed every month (275)
  2. We also have the percent of time our employees spend doing non-billable work per month
  3. Let’s total these together to get the Total Hours billed per month

SumAbsolute1

  1. Click in Cell H2
  2. Multiply E10 and B13=E10*B13
  3. The result is 55, this is not the result you want. Since the Non-Billable number is a percent, you will want to multiply by 1.20 instead of 0.20 to get your Total Hours.
  4. Change your formula to =E10*(1+B13)
  5. Now that your formula is set, drag it to the right to fill in the Total Hours for February and March.

Your February and March data should be showing as 0 which is not right.
Why is it wrong?
Double click in I2 to look at your February formula.
SumAbsolute2

Notice how your formula is looking at F10 instead of the 275 in E10. This happened because you did not place an Absolute reference to E10 in your original formula.

  1. Go back to your H2 formula
  2. Click between the E and the 10
  3. Press F4 once
  4. You will see a $ appear to the left of E and a $ appear to the left of 10. This will “Lock In”, make Absolute, both the column and the row reference so that no matter where you drag the current formula, it will always reference E10
  5. Hit Enter to get out of your formula
  6. Drag your formula to the right again and you will see the correct Total Hours for February and March

Congratulations! You have made and Absolute reference and completed the calculation for Total Hours Billed!

SumAbsolute1

Leave a Reply

%d bloggers like this: