# Excel Cell References

May 06, 2012

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:

`=SUM(B2:C5)`

Then `B2:C5`

is referencing the cells in the worksheet from `B2`

(top-left-most
cell) to `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
blue.

The cell reference in this case is `B2:C5`

.

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 `B6`

like:

`=SUM(B2:B5)`

And then we copied that formula to cell `C6`

, the formula would change to:

`=SUM(C2:C5)`

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:

`=SUM($B2:$B5)`

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
the format:

`$[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
that `B6`

contains this formula:

`=SUM($B2:$B5)`

And then we’ll copy and paste cell `B6`

to `C6`

and let’s see what happens to the
formula in `C6`

.

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 `C6`

to `C7`

?

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:

`=SUM($B2:B$5)`

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 `C7`

?

The formula in `C7`

changed into:

`=SUM($B3:C$5)`

What happened?

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 `3`

.
Similarly, the bottom-right cell reference that was `B$5`

changed to `C$5`

because the column was a *relative* reference and so
moved *relatively* one column to the right, while the row *absolutely*
stayed at `5`

.

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:

`=SUM(B2:B5)`

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 `F4`

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!