EXCEL CELL REFERENCES

Lesson 16 - Excel Cell References. Relative and Absolute Cell References

EXCEL CELL REFERENCES

More often than not when we create a formula using one of Excels built-in functions we will be referring it to a cell or range of cells. An example of a single cell reference would be A1. An example of a range of cells reference would be A1:A10. For the first example we are referring to the content of cell A1 only, while in the second example we would be referring to the contents of cells A1, A2, A3, A4, A5, A6, A7, A8, A9, A10. Using the reference A1:A10 is just a simple method that Excel will automatically recognize. If we use the reference A1:C5 we are telling Excel to refer to the contents of cells A1, A2, A3, A4, A5, B1, B2, B3, B4, B5, C1, C2, C3 C4, C5. Relative and Absolute Cell References

Relative

In Excel there are two types of cell references, these are Relative and Absolute. We will first look at relative cell references. All the examples in the Cell References paragraph are what are known as relative cell references.

As you may remember in lesson 1 we discussed that each cell on an Excel Worksheet has it’s own unique address, e. g. A1 is the relative address of the first cell on all Worksheets, while IV65536 is the relative address of the last cell on all Worksheets. The reason why they are called relative is because they are relative to the cell they are used in. This will be best explained by a simple example.

In Cell A1 Type The Number 1 And In Cell A2 Type The Number 2.
Select Cells A1:A2 And Use The Fill Handle To Fill Down To A10, So That We Have The Numbers 1 To 10 In Cells A1:A10.
In Cell B1 Type This Simple Reference: =A1 And Push Enter. Select Cell B1 And Do One Of The Following
Copy And Then Select B2:B10 And Paste.
Double Click The Fill Handle.

You should now have the numbers 1 to 10 in both A1:A10 and B1:B10. This because we typed a relative cell reference in cell B1 (=A1), which is telling Excel to make cell B1 equal the value of the cell one column to the left on the same row i. e. A1. So when we copy the reference in B1 i. e. =A1 and paste it into cell B2 Excel is still going to reference the cell one column to the left on the same row i. e. A2. Copying the same cell (B1) and pasting it into cell B3 again tells Excel to reference the cell one column to the left on the same row i. e. A3.

Lets now copy the content of cell B1 and paste it into cell D1, this time we should get the result 0 (zero). If you click in cell D1 and look in the Formula bar you will see the relativecell reference: =C1. The reason we get the result of 0 (zero) is because the value of an empty cell is 0 (zero).

Absolute

Ok, let’s now look at what an Absolute cell reference is. Basically an absolute cell reference is a reference to a cell that does not change no matter where it is copied. Again this will be easier to see by using an example.

Delete The Contents Of Cells B1:B10 And D1.
In Cell B1 Type The Absolute Cell Reference =$A$1 And Push Enter.

Copy And Then Select B2:B10 And Paste.
Double Click The Fill Handle

This time you should have the number 1 in cells B1:B10 and if you select any cell in B1:B10 and look in the Formula bar, they will all have the absolute cell reference =$A$1. This is because by using the $ (dollar sign) we are telling Excel to always refer to the same cell no matter where we copy this reference to. The $ in front of the A ($A) is telling Excel to make the column reference absolute, while the $ in front of the 1 ($1) is telling Excel to make the row reference absolute. So the reference in its entirety is what is known as an Absolute cell reference.

If you grasped this concept we can move on to what is known as a either an:

Absolute Row Relative Column Reference Or;
Relative Row Absolute Column Reference.

Again the best way to see this is by using a small example.

Relative Row Absolute Column Reference

Try this:

Delete The Contents Of Cells B1:B10.
In B1 Type The Relative Row Absolute Column Reference: =$A1 And Push Enter.

Copy And Then Select B2:B10 And Paste.
Double Click The Fill Handle

You will have the numbers 1 to 10 in cells B1:B10. This is because the row portion of the reference (1) is relative. Now copy cell B10 to cell D1 and you should get the result 1. This is because the column portion of the reference ($A) is absolute. If you click in any cell in the range B1:B10 or D1 and look in the Formula bar you will see that the row portion is always relative to the row the reference resides in, while the column reference is always absolute.

The same principle also applies to any reference that has an absolute row relative column reference. Again this can be best seen via the use of a small example.

Absolute Row Relative Column Reference

Try this:

Delete The Contents Of Cells B1:B10 And D1.
In B1 Type The Absolute Row Relative Column Reference: =A$1 And Push Enter.

Copy And Then Select B2:B10 And Paste.
Double Click The Fill Handle.

You will have the number 1 in cells B1:B10. This is because the row portion of the reference ($1) is absolute. Now copy cell B10 to cell D1 and you should get the result 0, this is because the column portion of the reference (A) is always relative to the column the reference resides in. If you click in any cell in the range B1:B10 and look in the Formula bar you will see that the row portion is always absolute. If you click in cell D1and look in the Formula bar you should see =C$1