Search form

Absolute and relative references in Excel

When you create a formula in Excel that refers to other cells in the worksheet, Excel will store the information about those cells as relative references. Relative references and their counterpart, absolute references, are one of the things that make spreadsheets such a powerful tool.

Relative references

A relative reference allows you to describe a cell in terms of where it is relative to the current cell. If you have D14 selected, then D15 can be described as the cell one row down. G10 can be described as the cell that is 3 cells across and 4 cells up from the location of G10.

For example, suppose you put a formula in cell A1 that looks like this:

=B1+B2+B3

Excel actually thinks of these cells, B1, B2 and B3, in terms of where they are relative to A1. So B1 is actually 1 cell to the right and 0 cells down. B2 is actually 1 cell right, 1 cell down, and so on.

Copying and pasting a formula containing relative references

When you copy the formula from A1 to A2, Excel will adjust the formula automatically to look like this:

=B2+B3+B4

As far as Excel is concerned, this formula simply says: take the value from one cell to the right and add it to the value from one cell to the right and one cell down and add to that the value from one cell to the right and two cell down. You can copy this formula to any other cell in the spreadsheet and it wll always add up the same set of cells relative to the cell that contains the formula.

Absolute References

Sometimes you may not want to use relative references. For example, you may have a unit price for a product entered into a specific cell, and you want to be able to write a formula that will always refer to the cell containing the unit price wherever you copy it to. To do this, you need to identify the unit price cell in your formula as an absolute reference.

Cutting and pasting a formula containing absolute references

For example, suppose the unit price is stored in cell A5. You want to create a formula in cell A6 that will multiply a quantity stored in B10 by the unit price using an absolute reference. This formula would look like this:

=$A$5*B10

The $ signs tell Excel that A5 is an absolute reference. When you copy and paste this formula from A6 to A7, the formula will change as follows:

=$A$5*B11

Because you copied the formula to the cell in the next row down, B10 has changed to B11. But the reference to A5 hasn't changed because you identified it as an absolute reference.

Here's a quick tip. When entering A5 in the formula, press F4 to convert it to $A$5. If you're using Excel on a Mac, press Command+T

More on absolute references

Note that there are two $ signs in $A$5 for a reason.

$A means "wherever you copy and paste this formula to, this reference will always refer to column A"

Similarly, $5 means "wherever you copy and paste this formula to, this reference will always refer to row 5".

Here's an example of how this might work.

Suppose you have the following formula:

=$A5*B10

You then copy and paste this formula into the row below. The formula now looks like this:

=$A6*B11

Note that A hasn't changed because you "locked" it using $A. However, you didn't lock the row number, so it was increased by one when you pasted it in to the row below.

Now imagine you copied and pasted the formula into the next cell across. Now the formula looks like this:

=$A5*C10

Because you pasted the formula across one column but kept it in the same row, $A5 doesn't change. However, B10 changed to C10 because the formula was pasted into the next column along.

Cutting and pasting formulas

When you cut and paste a formula, absolute and relative references within the formula behave the same - they don't change. For example, if you have the following formula:

=A5+A6+A7

and you cut and paste it into another cell - any other cell - in the worksheet, the formula will be unchanged.

Similarly, the following formula will not change if you cut and paste it into another cell in the worksheet:

=$A$5+$A$6+A7

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.

Add a comment to this lesson

Comments on this lesson

Thank you

Thank you very much. This helped out quite a bit. I was trying to reference distance between mile markers and could not figure out how to reference just one mile post.

Ex. Distance between 2 points - Anna MP3.0, Bess MP3.2, Charlie MP4.0

if anna is A1, and Bess is A2, and Charlie is A3, the formula for the cells you are trying to fill with distance between looks like this:

=SUM(A2)-$A$1
=SUM(A3-$A$1

Using an absolute cell with anna's milepost, you can generate and copy the formula to determine the distance between anna and bess, and anna and charlie, and so on.

Thank you very much!!

Great read and extremely

Great read and extremely informative and helpful.
My only feedback would be to provide picture examples.
Otherwise, it was well written.

VERY HELPFUL!

Thank you for this quick and concise tutorial. I've been having wrong results in my tracker. Thank you @fiveminutelessons for posting this. It really helped me a lot because you've provided an easy-to-follow process and examples. I feel smarter with absolute references in Excel now because of this! ;) Keep up the good work! :)

What an explanation

I consider myself a little bit of a donut when it comes to understanding something new, but this was one of the most concise tutorials I've ever read.

Fantastic examples which cover everything I needed to know.

Thank you!

What an explanation

I consider myself a little bit of a donut when it comes to understanding something new, but this was one of the most concise tutorials I've ever read.

Fantastic examples which cover everything I needed to know.

Thank you!

typeo

=A5+A6+A7

and you cut and paste it into another cell - any other cell - in the worksheet, the formula will be unchanged.
I believe you want the last word in this sentence to read changed not unchanged.

Add comment