We're back. You've just worked through for

practice videos and you've seen how Excel handles functions and formulas.

Now, we're going to talk about absolute cell referencing.

Which I call the dollar thinking.

Nicky, tell us about the dollar thinking please.

Thanks [inaudible].

Before we can get stuck into the dollar

thinking better known as absolute cell references,

it's important we understand what relative references are.

Now you guys have been using relative references without even realizing it.

When you put in a calculation and copy it down,

Excel uses relative references.

You might have noticed that you might have typed equals sum B4 to E4 copy down.

But when you check the second row,

it actually says equals some B5 to E5 now that's some weird kind of copy.

What is Excel actually doing?

What we need to understand is;

when we type equals sum B4 to E4,

Excel understands this as add up

the four cells to the left of me and that is why when you copy it down,

it carries on going up,

add up the four cells to the left of me and it works beautifully.

So this relative cell referencing is one of the things that makes Excel so powerful.

The only problem is,

it doesn't always work, does it?

Yes. For example, there could be

an instance where I'm typing a formula in a particular column,

and this formula is referring to data in column B as well as

the particular cell in column J and this data is in cell J4.

If I type a formula equals

B4 times J4 and if I fill that formula down just like you've seen,

you know that what will happen is that Excel will fill the formula

down into the next cell and call it equals B5 times J5.

I like that Excel has understood that I need B5,

but I don't want Excel to change J4 to J5.

I really need to tell Excel to lock J4 for me.

So what I tell Excel is to lock J4 by using the dollar symbol.

Instead of telling Excel equals B4 times J4,

I type equals B4 times $J, $4.

If I now fill this formula down exactly

the same way that you did in the previous few videos,

Excel understands that instead of going equals B5 times J5 in the next cell,

it will intuitively put in the next cell equals B5

times J4 and if you fill the formula all the way down,

you'll see B6 times J4,

B7 times J4, B8 times J4 and so on.

This is absolute cell referencing.

Excel is relatively referring to cells in column B

but absolutely referring to the cell in J4.

I hope that makes sense Nicky.

Yeah, very good, well done.

Excellent explanation.

Is there anything else we need to cover this week though?

That one last topic.

One of the things that people find that have to do quite often

is use values in other worksheets in their calculations.

Now actually, it's not tricky but it's very often not taught.

So people make the mistake of copying those values across,

and yeah, that can cause real problems because then

your workbooks get out of sequence here, very messy.

So what we're going to do is show you not only the better way but

the easier way to work with values in other spreadsheets.

So you've got two videos left for you to work through to complete this week.

One on absolute cell referencing and one on calculations across sheets.

Practice alongside Nicky which means you'll need to download

the Excel files and work through them and when you finish that,

practice by yourself as well.

So now over to you.