 In this trading video, we're going to look at the use of cell referencing in formulae. So what we're trying to do in column E starting on E2 is trying to find out what each of the regions are for the sales are the percentage of the total sales. So we're trying to find out what north sales are and percent of total sales, east sales against total, northwest against total, so on, so on, so on. So everyone is going to be looked at against the total sales. So if we look at how Excel will do the formulas, we'll start with an equal sign. It's going to be D2 divided by D8. These have been formatted to the percentage, but the idea behind the formula is to copy down. We don't want to do this one at a time. You could do it one at a time and just repeat the process, but that's going to take a long time. And if you've got the hand with the D to do, it doesn't make sense to do it one at a time. So you want to copy this down. And when we copy it down, okay, there's a lot of error messages. So div zero means divide by zero. So what's happening is as we come, as we copy down one cell, it's looking at the east region, but then Excel is moving away from the total because it's keeping the formula pattern reset. So we've set the formula pattern for the first formula, B2 against D8 divided by D8. Well, the next one should be B3 against D8, next one, D4 against D8, and then it repeats the pattern, D5 against D6, D7 against D8, and then D8 against D8 for 100%. Now the computer in Excel, and the calculator behind Excel, doesn't know to hold D8 in place. It just copies a pattern. So all it's doing now is saying, okay, B2 divided by D8, if it moves one down, that means it's going to be B3 by B9, it moves two down, it's B4 by B10. So it's just moving according to the pattern we originally set. We could correct this by going to all these div zeros and just going back and saying, well, I don't want it against B9, which is zero here. I wanted to go against D8, so we could now manually change all these back to D8. I've worked with companies in the past that have actually done this, and I've done this for hundreds of rows, so you can see how long this might take and the risk of error. So just a couple more, you can see what I'm doing, I'm just going back against D8 all the time, which is a total, and the final one, D8. Okay, so we know that by copying it down, Excel doesn't automatically give us the right answers. We know we can correct it, but correcting is okay for a couple of rows like I've done in this example. If that was 2,000 rows, that would not be appropriate, and it would lead to error and frustration. So we need Excel to help this and be a bit more helpful to us in copying these kind of formulas down. So I'm going to delete all the corrections I've made, and go to the 10.64%. Look at the formula, and think which part of that formula do I need to hold in place and do I need to lock? Well I need the B2 to move down to B3, B4, B5, B6, B7, and B8, so I need it to cycle down column B from B2 to B8. But the B8, which is the divisor part of the formula, I don't want that to move away from the A, because that is the total. So we can hold this in place by using dollar signs. Now if you type in a dollar sign normally, so let's escape this for a second, and I'll go into a different cell, if I type in a dollar sign and type in 1, 2, 3, a dollar sign with standard type in means currency. But dollar signs in formulas do not mean currency, they've got a different meaning, and I'll explain that now. So if I go back to the calculation, I want to put dollars around the B and the B8. So I click before the B, and I'm going to press a key, that's F4. Now F4 works on all desktop computers, on the majority of laptop computers, but there's some laptop computers, you've got to press function F4 to get dollar signs, and advising it used to the F4 key is a much better way of learning dollar signs instead of put them in yourself. So one press of the F4 gives you, in effect, a dollar before the B and a dollar before the 8. Now dollars inside a formula, we know we're inside a formula, so any dollars inside of the formula, they're like keys, they lock in something shut. So the dollar before the B locks the B shut, the B's a column, the dollar before the 8 locks the 8 shut, the 8 a row, if you lock the column on the row, you lock in a cell. So the first press of the F4 is known as absolute cell referencing, dollar, dollar, it locks the actual cell. If I press F4 again, the referencing moves. This is called relative cell referencing. So the B now is unlocked, it can move to C, D, it can move across, but the 8 cannot move from row 8 because it's got a key in place and the dollar's before the 8. Press F4 again, the dollar's now before the B. So the B can't move, but the 8 can go 9, 10, 11, so it's got no key against it. Press F4 again, it gets rid of all your dollar signs. So the F4 cycles through all your dollar signs. In our example, we want to hold the cell in place because we want to hold the cell B, 18th place so it doesn't move. So it's a dollar, dollar, press enter, copy down. And I can always get the formatting back by using the format painter quickly. And then just changing the currency to a percentage. You can quickly get formatting back if you ever need to. I can put it to two places if I want to. And there we go. So now we can see that every formula is divided by B8 because it's held itself in place. You can see B8 is held at 100%. It's B8 divided by B8 gives you 100%. So in this video, we've looked at the use of cell referencing and to get the right answer for the formula we wanted here, we have to use what's called absolute cell referencing. An absolute cell referencing is dollar, dollar and it holds a cell in place. So that's what we've done in this example. But this completes the training video for the introduction of cell referencing and this example, looking at the F4 key and the different choices we've got. Plus we've covered absolute cell referencing.