## 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:- Copy and then select B2:B10 and paste.
- 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.

#### Try this on your own

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

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