02 - Cell References

Referencing a Cell or Range

More often than not when we create a formula using one of Excels built-in functions we will be referencing a Single cell or a Range of cells.

• A single cell reference would be =A1.
  • Here, we are referring to the content of cell A1 only
• An example of a range of cells reference would be =A1:A10.
  • Here we are 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.

Explainer Video

Relative vs. Absolute Cell References

Relative

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

Try it yourself!

Launch your Excel application

• 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:
  1. Copy and then select B2:B10 and paste.
  2. Double click the Fill Handle.

What did we just do?

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 B (=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.

Now, 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.

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

Try this on your own

• Delete the contents of cells B1:B10
• In B1 type the absolute row relative column reference: =A$1 and push Enter.
• Select cell B1 and either; o 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.
  • 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.

Explainer Video

💡 TIP

• 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 D1 and look in the Formula bar you should see =C$1

Absolute Column Reference (with row as relative)

Example =$A1

Try this:

• Delete the contents of cells B1:B10
• In B1 type the Relative row absolute column reference: =$A1 and push Enter.
• Select cell B1 and either:
• Copy and then select B2:B10 and paste.
• Double click the Fill Handle

What did we just do?

• 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.

💡 TIP

⇛ Avoid Typing Whenever Possible ⇚

Do not type out a cell reference: Simply type an '=' and then click on the cell you want to reference.

Toggle Through Absolute and Relative References

A highly recommended habit to form is to use Function key F4 to toggle through absolute to relative references.

This again will save typing and help prevent errors.

Try this on your own

• In cell B2 type = and then move your mouse pointer over cell A1 then select it .
• Now click within the Formula bar so the mouse insertion point is either:
  • Immediately before A1.
  • Between the A and the 1.
  • Immediately after A1.
• Now push the F4 button and your reference should change to absolute i.e. $A$1
• Push F4 once i.e. A$1
• M Push F4 again as you will get an absolute column relative row reference; ie. $A1
• Push F4 again and we will return to our original relative reference; ie. A1.

So as you can see: By pushing F4 we can toggle through relative to absolute reference very easily.

Test your knowledge