Microsoft Excel MO-200 – Level 2, Section 5- Utilise cell ranges and references informulas and functions

1. Copying and pasting basics, and Utilising references (relative, mixed, absolute)

Welcome to level two, session five. And this is the session level two about formulas. And what we’re going to look at in this session is one of the most important concepts about formulas. And a lot of people who use Excel just don’t get this. And it’s crucial. This is all about cell references and their relationship to other cells. So I’m going to give you an example. This is a brand new workbook that I’ve created so you can download it as a resource to this video. So think of a number and double it. So I’m going to think of the number six, but I’m going to double it. So my answer is twelve. So I want this to be done in a formula. So pause the video. What formula do I write? So we cover this on a previous level. I say equals. You must have an equals or plus sign at the beginning. Be free. Or I could click on be free. And then times two. And there we are, all well and good. Now in Excel, you can copy and paste. Just like in word. Copy puts the information in a clipboard and paste retrieves it. Now in Word, it just captures what you got and then spits it back out. It’s English text. The computer doesn’t understand English because it’s just a parrot. In Excel, it does something a bit more clever.

You’ll see what I mean? I’m going to copy and that’s control and C, if you’re looking for the shortcut. Now I want B five to say twelve as well. I want it to be six times two. So I’m going to go to paste. So we’ve got these little lines around B four. That is what is in the clipboard. So I’m going to go to paste, and I’m expecting it to say equals B three times two, because that’s literally what we’ve got in b four. So copy and paste this big paste button. And I’m going to be disappointed because it doesn’t say B three times two. It says B four times two. Why is this? It’s not an exact duplicate. It’s because what the computer is saying is, okay, you want the cell that’s immediately above doubled. So when you copy and paste it into b five, the cell above is doubled.

In other words, it’s giving you a relative correlation between the cells. So b four is my target cell and the one above. And so it’s b five and the one above. Similarly, if I start copying to the right so copy and paste, it’s not going to say equals B three times two. It’s going to say C three times two. And there are times when you might actually want it to say, yes, that’s what I want. But then if I wanted it to say twelve and I copied it further and it now says zero because zero times two is zero, I’m going to be really disappointed. So what can I do well, what I want to do is say to the computer, I’m saying equals B three. I really mean b three. Not the sell above b four, but actually sell b three four. Stop. And the way that this has evolved over the 1980s is by the use of dollar signs. So this is nothing to do with dollar signs as in dollar bills. This is something else.

Now you can put a dollar sign before the rule number or you can put a dollar sign before the column letter or you can do one before both. So if I wanted to say b three and nothing else, then I would put a dollar sign before the B and a dollar sign before the number three. So what the dollar is doing is saying, I really want this, so I really want column B, I really want row three. So now when I copy and paste instead of it being b four, saying equals B three times two. So the cell above, if I copy it and paste it into B five, it’s still saying equals b three. Each of these elements are fixed by the dollar sign. I want to lock them in so I can copy it to the right and it’s still referring to sell b three.

Now there are times when you might not want to lock in the raw as well as the column. So for instance, I’ve got the number eight in C three and I want to double that. So equals C three times two. So let’s just have a look at the formulas. Equals B three times two and equals C three times two. Can you see what is similar with these two formulas, the times two and the number three? In each case I want the raw number to always be raw three. So what I’m going to do is I’m going to put a dollar sign before the three so that says wherever I copy it, I want it to be raw number three. So if I now copy this down, it still refers to b three. If I copy it right, it refers to c three. I haven’t got a dollar sign before the C. So I’m saying I’m okay for the column to change, but I’m not okay for the row to change. So let’s have it the other way around. Think of a number and double it.

So what I put is equals a eight times two. Because the number six is in a eight, I want it doubled. But if I was to copy that into cell C eight, it would be saying, okay, I want the cell to the left of where I am and I want it doubled. So if I copy that into C eight, it’s saying I want the cell that’s left of me and I want that doubled. So instead I want to say I want something in a eight to be fixed. So can you pause the video, work out what you want to be fixed and where you want the dollar sign to be. You want the column A to be fixed. So when I copy it, it says equals B eight.

And I don’t want it to say that. I want it to say A eight. So let’s put the dollar sign immediately before the letter A. So now when I copy it, it’s going to stick. Referring to the column A, if I copy it down the row can change, but not the column. So you can see that this is a very, very important topic. To ensure that we’ve got the ability to see exactly the right row in the right column. So the dollar goes before the I want this to be unchanged. It’s a very, very important topic. So let’s have a practice activity based on this topic.

2. Practice Activity – multiplication table

Right here is the practice activity. And don’t forget, you download this spreadsheet or you can download the spreadsheet attached to the previous video. So this is a multiplication table. So what we’ve got is numbers going to the right and numbers going down. And this is the answer that I want you to get. So two times two is four. Four times two is eight. Six times five is 30. Now, you’ve noticed I’ve not actually given you the answers. I want you to work it out. Now, I don’t want you to type in lots of formulas. Instead, I want you to just type in one formula only and then copy this all over the place. So if I was to type in equals two, equals two times two and copied that over the place, then that would be wrong. But that would be a quick way of copying an answer everywhere.

So you can only use one formula. It’s very simple once you get the hang of it. If you haven’t got the hang of it, then the answer is very difficult. So please download this spreadsheet, do the answer, make sure you get it right, and I’ll see you on the next video if you’re completely stuck. And just one final word on terminology, and this just gives you maybe a little hint. There are three types of things you can do with the dollar signs and they’re called relative, mixed and absolute. Relative is when you use zero dollar signs, you’re not having the row fixed or the column fixed. Mixed is when you’re using $1 sign. So having the row fixed and the column floating or the column fixed and the row floating and absolute is when you use two dollar signs when you are fixing both the row and the column. So this might give you a clue if you get stuck on how to do this. Remember, mixed is $1 sign. Good luck.

3. Answer – multiplication table

Right. So how did you get on? Hopefully you were able to do it, but if not, here’s what you needed to do. So what I need to put in here before is two times two. And I’m putting it in as text. So here I need two times three, two times four. So what is common with all of these? The number two at the beginning. So which number two are they referring to? Is it the two here in B three? Or is it the two here in A four? And the answer is the two here in A four. So we need to multiply by something which fixes column A. So here’s the standard formula equals a four times B three. Now how can we fix column A? So it doesn’t move. We put a dollar sign before it.

So now if I copy this all the way across, you can see that this works for row four. However, if I copy this down, it doesn’t work for any of the additional what rows. Three times two is not twelve. So let’s just have a look at the formulas. And you can see here this formula is equal to a four times B three. This one is a five times B four. Which one of those two is wrong? A five or the B four? It’s the reference to the B four. It should always refer to this B three up here, this header roll. So how can we fix remember, we only can use one formula. How can we fix this so that we’re always referring to row three? The answer is we need to put a dollar sign before the three so equals dollar A four times B dollar three.

So this says give me the value from column A and multiply it by the value from row three. So now if I copy this and paste it, you can see that it now works as you would expect. So you can see how important it is to get the dollar signs the right way around. And in fact, you use dollars at all. If you had to use this to create all of this without any dollar signs, then you’d have to write 121 different formulas. Here I only have to write $1 sign one formula or two dollar signs, one formula and copy it. And you can use dollar signs. Of course, we’ve mixed and absolute references. Absolute is saying this one sell only, so I sell a one. I always want it to be a one.

So if I wanted to add the number one to each time, I could say plus dollar a dollar one, I always wanted to refer to a one. So here in this cell I’m doing eight times eleven and add one. So in this particular instance, I have got two mixed references. In other words, the column is fixed, but the raw number is floating. Multiplied by the column is floating relative to the target cell. But the raw number is fixed. And this is your answer. So you can see that using the dollar sign will save you a tremendous amount of work. And we’ve already seen the dollar sign being used, like for instance, in the paid setup. So here you can see that it says rows one to three all the time. You can see the dollar sign before the row. So I hope you found this particular exercise useful. The dollar sign crucial when you get to the more advanced parts of Excel that we’ll start to have a look at in level three.