Referring to another location
Microsoft Excel uses the “A1” format in cell
The alphabet (“A”) refers to the column
position, while the number (“1”) refers to the row position.
If you key in the formula “=A2”, it will refer
to and display the cell value in the Cell A2 (Column A, row 2). And when you copy the cell to other
places, the reference will move according to the row and column (see picture above). This is relative referencing (the
reference is relative to the position of your cell). However, there are times when we want to
fix the reference, have the cell point to the same location even if we copy and
paste to other places.
Microsoft Excel uses the Dollar sign (“$”) to
fix the reference of a cell. Using
the same example above, by keying in “=$A$2”, we will still refer
to and display the cell value in the Cell A2.
But when we copy it to other location, the reference no longer moves, and is
Combining both Absolute and Relative Referencing
Microsoft Excel allows you to fix either the column or row
reference by placing the “$” in front of either.
In the example above, I’ve fixed the row reference, so
when I copied the cell across the area, the reference cell will follow the
column, but will remain pointing to row 2.
In this next example, I’ve fixed the column reference,
so when copied across, the cell reference follows the row, but remains pointing
to column A.
You can use the “F4” key to cycle through the
various absolute referencing options when you key in formula. Just make sure that your cursor is
pointing at the correct cell in the formula.
In this example, we have 3 products being purchased by 3
customers and we want to know how much each customer has to pay.
Because the price is in column B, when calculating the
amount for each customer, we have to fix the reference to the price in column
B. As above, in my formula, we fix
the cell reference as $B3, so that when we copy across to the rest of the
cells, it will follow the customers and product, but remain pointing at the
correct price column.