EXCEL CELL REFERENCES
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.
E 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 relative cell 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.
Select cell B1 and
either
- 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.
Comments
Post a Comment