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