Absolute and Relative Referencing

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.

Absolute Referencing

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
now Absolute.

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.

Keyboard Shortcut

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.

Practical Examples

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.


Tagged: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: