When writing Excel formulas, you’ll definitely be making references to other cells (like =SUM(A1:A3)). If you haven’t heard of absolute cell references and relative cell references, you’re going to want to read on, because this knowledge can save your life! (nah, it probably won’t :) but it will save you a lot of headaches!)
I touched on cell references previously, however in this post I would like to go further in-depth on the subject. In this post, we’ll be talking about:
- What are cell references?
- Relative cell references
- Absolute cell references
- Keyboard shortcut to toggle between absolute and relative references
What are cell references?
Let’s get a base of what cell references actually are. A cell reference is an
area on your worksheet that you are referring to in your formulas. For example,
if you have a simple
SUM() formula like this:
B2:C5 is referencing the cells in the worksheet from
C5 (bottom-right-most cell). When editing your formula, the cell
reference will be outlined in a specific color. In this example, it’s shown in
The cell reference in this case is
OK, now that we have that basic understanding, let’s look at what relative references and absolute references are.
Relative Cell References
Relative cell references basically means that if you were to copy and paste a formula that had relative references in it, then the formula’s references would change, relative to where you copied from.
For example, if we had a formula in cell
And then we copied that formula to cell
C6, the formula would change to:
Here’s an animated example:
The format of relative cell references is:
[Cell Column Reference][Cell Row Reference] (i.e. B2)
However, absolute cell references have some extra formatting. Let’s look into that and see what’s going on.
Absolute Cell References
Let’s start by showing the format of absolute cell references. Going back to our
previous example, if we set the formula in cell
B6 to be:
The difference is with the dollar sign
$. This is a special character for Excel
formulas which will “lock” the cell reference that you are referring to. Here is
$[Cell Column Reference]$[Cell Row Reference] (i.e. $B$2)
Notice that I put a dollar sign
$ in front of both the column reference as
well as the row reference. This is because you can lock either the column
reference, the row reference, or both.
What do you mean by “lock the reference”?
It’s easiest to show you with an example. Let’s go back to our example and say
B6 contains this formula:
And then we’ll copy and paste cell
C6 and let’s see what happens to the
The value in
C6 is the same as in
B6, so what’s going on here?
Because the formula had
$ in front of the column reference, then what happens
is that the column’s reference is absolute, meaning that it will not change
relative to where you pasted to, as shown above.
However, we didn’t lock the row reference, so what would happen if we copied
The rows have adjusted in the formula in
C7. Notice that they have adjusted one
row down, which is one row relative to where you copied the formula from.
So, we copied the cell
C6 and pasted one row down to
C7, where the row
references have adjusted themselves to move one row down along with you (hence,
making them the relative references).
Now, you can make whatever column or row reference absolute or relative. You can even mix and match them if you like (and sometimes, it is helpful to do so in some formulas). Let me illustrate this for you with one more example.
Let’s change the formula in cell B6 to be:
So, the top-left cell reference is locking the column, and the bottom-right cell
reference is locking the row. What do you think will happen if we copy and paste
this formula one column to the right and one row down to cell
The formula in
C7 changed into:
When we copy and pasted one column to the right and one row down, the top-left
cell reference of
$B2 locked the column, but not the row. So the column
stayed absolutely as column
B, but the row changed relatively to row
Similarly, the bottom-right cell reference that was
C$5 because the column was a relative reference and so
moved relatively one column to the right, while the row absolutely
I know it’s a bit confusing at first, but try making some examples of your own and play around with it for a bit to get used to it.
Keyboard shortcut to toggle between absolute and relative references
A nice trick that you can use is the F4 key on your keyboard to cycle through different reference modes in your formula.
The reference mode cycle goes like this (each time you press F4):
- Rows and columns are relative
- Rows and columns are absolute
- Rows are absolute, columns are relative
- Rows are relative, columns are absolute
If we go back to our simple formula of:
And if we edit the formula in the formula bar, we can highlight the top-left
cell reference, the bottom-right cell reference, or both cell references and
toggle through the different reference modes (absolute or relative) with the
key. Here’s an illustration to make it a little more clear (NOTE: each time you
see the cell reference changing, I’m pressing
F4 on my keyboard):
I hope you have found this information useful. Please comment below if you have any questions or tips you’d like to share!