Relative and absolute explained
Relative
The easiest way to explain relative and absolute cell references is with the following visual:
With a relative cell reference, if I were to take cell F2 and autofill it to the surrounding cells, check out how it becomes exactly the same as the diagram to the left of it.
Therefore...
Relative cell references change column/row values when copied to a different cell.
Absolute
Let's do the exact same thing, except this time with an absolute cell reference.
Everything becomes "Center". Why? Because...
Absolute references do not change column/row values when copied into a different cell.
Now that we have visualized relative vs. absolute cell references, let's learn how to code them.
Coding a relative cell reference
If I told you that I want to reference the value of B2 in F2, what should I type into F2?
I should type...
=B2
...since that's where our desired value of "Center" is!
Remember, when referencing a cell, always start with a "="!
If you do that, you'll legit end up with "B2" in your cell, which is not referencing anything.
How can we know for sure though that we relatively referenced B2?
Try autofilling (by click and hold the little green box in bottom-right corner) F2 into E2. You'll get the following:
This successfully matches what we experienced in the example at the start of this article. But how?
To illustrate what's going on behind the scenes, let's first remind ourselves that F2 references B2.
Even though we copied the value of F2 into E2, E2 does not reference B2. It actually references A2, since we moved one column to the left.
See how our column changed since we copied a relative cell reference into a different cell?
We can do the same thing with rows. Autofill the value of F2 into F1. You'll get the following result:
Once again, notice that our copied value does not reference B2. It actually references B1, since we moved one row upwards.
Coding an absolute cell reference
Now, instead of relatively referencing B2 in F2, let's absolutely reference it.
How do you code an absolute reference?
For absolute references, place a "$" before the column and row values.
So, if we wanted to absolutely reference B2 in F2, we'd type the following into F2:
=$B$2
Notice how we typed the "$" before the column (B)...
=$B$2
...and before the row (2).
=$B$2
Now, if we autofilling the value in F2 into E2, we won't get "Left" like we did before...
...we'll get "Center". That's because we are absolutely referencing the column...
=$B$2
...so even if the cell gets copied to a different column, it will remain the same as the original absolute cell reference.
The same will occur if we copy the value in F2 into F1, since we're absolutely referencing the row.
Challenge question
What do you think will happen if we remove the column absolute reference and try copying F2 into G2?
=B$2
The copied cell does not stay "Center" as it did before, it becomes "Right". That's because removing the "$" from the column value made it a relative column reference.
If we copy this cell downwards, it will remain "Center". That's because the row reference is still absolute.
When to use relative vs. absolute?
Relative case
Here's a prime situation for a relative cell reference:
Notice how the values in column "c" only reference "a" and "b" values within their own row. A relative cell reference enables me to autofill the list and each "c" value to only use relevant "a" and "b" values in the same row.
Relative cell references are best used when you want to autofill down a list, utilizing respective values for each row and/or column.
Absolute case
Here's a prime situation for absolute cell references:
Notice how that interest rate is going to stay in cell F1, and I want to apply it to every loan in the list. This means I should absolutely reference it as I autofill the list so that it continually references the same cell.
Absolute cell references are best used when you know the cell location won't change for a necessary value.
Practice problem
Determine the final cost for each of the separate orders, while considering that the sale amount of 15% in cell H1 is consistent for each order.
=C2*D2*(1-$H$1)
The above formula should then be applied to all the cells in the "Order Cost" column.
Note: You can't just multiply by the sale percentage! Doing so just shows how much money the user is saving on the sale... not the final order cost. Subtract that percentage by 1 to determine the order cost.
Next, let's learn about the RANK function!
Excel Cram Kit (Exam 1)
Want to unlock 
content? Get your Excel Cram Kit (Exam 1) now!
 | Apply | PRACTICE EXCEL EXAM 1 (PREVIEW ONLY) |
 | Tools | Excel Nerd Notes (Exam 1) (PREVIEW ONLY) |
 | Lesson | YEAR, MONTH, DAY |
| Lesson | SUM & AutoSUM |
| Lesson | Relative vs. Absolute Cell References |
| Lesson | RANK |
| Lesson | COUNT |
| Lesson | ROUND |
| Lesson | WEEKDAY vs. WORKDAY (PREVIEW ONLY) |
| Lesson | VLOOKUP (PREVIEW ONLY) |
| Lesson | PMT (PREVIEW ONLY) |
| Lesson | IF (PREVIEW ONLY) |
| Lesson | Percent change (PREVIEW ONLY) |
| Lesson | Excel charts (PREVIEW ONLY) |
| Lesson | Importing text files (PREVIEW ONLY) |
Excel Reviews (Exam 1)
"I bought this Cram Kit the night before my exam, and it helped me tremendously. It provides step-by-step explanations for all the units covered in class."
Matt Lamanna
Oct. 14, 2021