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

**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.

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

- Click in Cell
`H2`

- Multiply
`E10`

and`B13`

,`=E10*B13`

- 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.
- Change your formula to
`=E10*(1+B13)`

- 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.

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.

- Go back to your
`H2`

formula - Click between the
`E`

and the`10`

- Press
`F4`

once - You will see a
appear to the left of`$`

`E`

and aappear 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`

- Hit Enter to get out of your formula
- 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!**

Categories: Excel

## Leave a Reply