Lesson 1, Topic 1
In Progress

1.13. Demonstrate the difference between relative and absolute cell addressing by using it in a formula

ryanrori February 3, 2021

[responsivevoice_button rate=”0.9″ voice=”UK English Female” buttontext=”Listen to Post”]

A reference in excel “identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula.”

So what is the difference between relative and absolute references?

When you say a reference is relative, you are telling excel to adjust that reference in formulas based on where you move or copy the formula. We will use our multiplication tables to demonstrate this concept.

If you look at our example you will see we have a formula in cell B2 as =B1*A2 and now if you copy paste this in another cell, let’s say, C1, the new formula would read like =C1*B2. 

This graphic displays the formulas. The next graphic shows the answer. You will notice that the answer in C5 seems correct. However, the answer in cell D3 is not correct. 

This happens because the formula we copied states that it referenced one cell to the top, multiplied by one cell to the left. So cell B2 equals 1*1=1, which is correct. Cell C2 equals 1*5=5, this is also correct, but you will notice that it is using the wrong 1 in the formula. C3 demonstrates this best because we D3 equal 5*3=15 and not 1*3=3 as we would expect.

When you say a reference is absolute, you are telling excel not to adjust that reference in formulas when you move or copy them.

Switching between relative and absolute references:

While editing the formula you can use F4 function key to change the reference of a cell on which cursor is focused. By pressing F4, excel switches the references between relative (A2), absolute ($A$2), relative column and absolute row (A$2) and absolute column and relative row ($A2).

We will demonstrate this by using the same example as before:

We cannot use absolute reference for this formula either. This is what would happen if we do:

The result is that all the answer is 1 because we keep on referring to the exact same cells. What we should do in this formula is to lock absolute column and relative row($A2) and relative column and absolute row (B$1). So the formula should look like the following.

You will now be able to use the Autofill option to complete the table

2.4.5 Change data to test possible solutions to the given problem without having to change formulae

Pressing F2 when a cell is selected allows you to edit the formula directly. Any cell references used in the formula will be automatically highlighted and will appear in different colours:

These coloured squares are referred to as the Range Finders.

  • You can click and drag any of the coloured boxes to new cells to quickly adjust the cell references used in the formula.
  • The four squares in the corners allow you to increase or decrease the range of cells being referred to by dragging these squares with your mouse
  • As you change the data in the cell referenced you will see the answer changing automatically

This would not have been possible if you used values in your calculations rather than using cell referencing.