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
The below VLOOKUP formula references Cell
A2, and Columns J through M.
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
If I extend this formula to the right once, the new formula will be
If I extend this formula down once, the new formula will be
Making my original formula
=$A$2 causes both movements to stay as 5
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
- 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
- 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
- Click between the
- You will see a
$appear to the left of
$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
- 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!