Cell Referencing

Quick reference

• 00:04 In this video, we're going to look at absolute and relative cell referencing,
• 00:08 as well as a few different ways to copy formulas because these are both really
• 00:12 important to understand.
• 00:14 The goal that we're gonna try and set up here is to multiply the values in the left
• 00:18 most column by the values in the header row and
• 00:20 get the correct values all the way through the table.
• 00:22 So you think this is pretty easy to start with.
• 00:25 We can say = 10 x 1.
• 00:28 So A5 times B4 and hit enter.
• 00:30 And we want to copy this formula, so what we'll do is we'll select it and
• 00:35 we'll mouse over the bottom right hand corner to where our cursor changes to this
• 00:38 black plus sign.
• 00:39 And I'm gonna left click and drag across and
• 00:42 that will extend the formula exactly as it was written.
• 00:46 And now we'll left click and drag down and the same thing will happen.
• 00:50 Now the hash marks come in because the number is too long here, and
• 00:53 the reason being is because when we copied this formula,
• 00:56 you'll notice it is no longer pointing to our column A.
• 00:59 And the reason is this is a relative formula.
• 01:02 When a formula is copied, Excel takes a look at the referencing style and says,
• 01:06 hey, this is relative so, when I put the new format in the cell I better make it
• 01:10 relative to the way the old one was written,
• 01:11 which was looking at the cell to the left times the cell above.
• 01:15 And you can see that as if was copied that's exactly what's
• 01:18 happened here is it's now always going to the cell to the left times the cell above.
• 01:23 Can we do better than that?
• 01:24 Well absolutely.
• 01:27 If we go over to the next column and we say you know what, let's go it again,
• 01:29 we'll say 10 x 1, but ranges are what I like to refer to as disloyal.
• 01:36 If you want them to stay pointed to one area, you need to pay them.
• 01:40 So let's put a dollar sign in front of the F and
• 01:42 a dollar sign in front of G and we'll pay them to stay put.
• 01:47 And we'll go again.
• 01:49 Drag this across and I'm gonna fill it down a little more quickly right now.
• 01:53 What I'm gonna do is I'm gonna double click on this black plus sign.
• 01:56 And it fills it all the way down the table.
• 01:58 Now, this might be a little bit better.
• 02:00 It might not be.
• 02:01 If we take a look in the next column here, we can see that we're always
• 02:04 pointed to the same column, except that it's staying with the same column here.
• 02:09 That's not so good.
• 02:11 But the rows are changing.
• 02:13 So that's kinda good and kinda bad.
• 02:16 So we need to see if we can figure this out to do a little bit better.
• 02:19 Well, the secret here is that we can actually pay, rangers are so
• 02:24 disloyal, we need to pay both the rows and columns to stay put.
• 02:28 So the way that we can do that is we can actually, if I press F4 here, or
• 02:33 you can put these in manual, you'll see that we can pay the columns and
• 02:36 we can pay the rows.
• 02:37 They are that just disloyal that they need to be paid twice.
• 02:40 And if I do that, and say enter.
• 02:43 I am now going to hit command C to copy and
• 02:47 I'm gonna drag over this whole thing and press command V.
• 02:49 So there is another way to paste.
• 02:51 You'll notice that, we've now locked in to a specific cell, no matter where we are.
• 02:56 So that's kinda good and kinda bad as well.
• 02:59 It's useful, but not in this particular case.
• 03:03 What if we went and just paid the rows to work.
• 03:07 Well, that's no problem, we'll say equals.
• 03:09 And I'm gonna press F4 and then F4 again.
• 03:12 And you'll notice that keep pressing F4 and
• 03:14 it cycles through all the different states that we can use.
• 03:18 We'll say times this one F four, F four.
• 03:21 There we go.
• 03:23 And now, what we're gonna do, is I'm going to double-click the fill handle this way.
• 03:28 It fills down and I'm gonna fill across to the right.
• 03:30 Unfortunately, there's no shortcut for doing that last part.
• 03:34 And you can see in this case That we've managed to lock the rows in place but
• 03:39 we haven't locked the columns.
• 03:41 And for this reason, we actually need to go to
• 03:43 a mixed referencing style where we control each piece individually.
• 03:49 So the way we're gonna do that is we're gonna say, you know, when I multiply 10,
• 03:53 I'd like to keep the columns absolute.
• 03:56 So I pressed F4 a few times to get just the columns anchored.
• 03:59 Now I'm gonna say multiply.
• 04:00 And we'll go up to number one in cell L4.
• 04:03 I'm gonna press F4 and say I don't want it to be always pointing to L4.
• 04:08 I want it to be a relative column, so
• 04:11 as I copy it this way L will change but the row will stay put.
• 04:15 I'm going to hit enter.
• 04:18 Now here's a neat little trick as well.
• 04:20 If I wanna commit the same formula, you'll see that this is going to work,
• 04:24 as I copy this across and down.
• 04:29 You'll notice that this is now working just beautifully.
• 04:33 But if I wanna copy this into all cells at the same time, I can actually do this.
• 04:37 I'm gonna clear these guys out just so you can see that this really happens.
• 04:40 I'm gonna select all these cells.
• 04:41 Notice the background of the first one I selected is a slightly different color.
• 04:44 This is what we call the active cell, and the entire block is the selection.
• 04:49 I'm going to go in to edit the formula.
• 04:50 And what I'm going to do is I'm going to commit this to multiple cells at
• 04:54 the same time.
• 04:55 Like this.
• 04:56 And the way I did that is I actually held down the command key and
• 05:00 pressed enter, when I was in edit mode.
• 05:02 So, let's try this one more time.
• 05:04 We'll grab those guys out.
• 05:06 I'll select the area that I want to fill first and then what I'm going to do is in
• 05:10 edit mode when writing my formula, I'm going to press Command Enter and
• 05:15 it actually commits the same formula to every cell within that block.
• 05:20 And you can see the way that we've mixed the referencing styles.
• 05:23 It's working quite nicely to give us the proper answers in every instance.

Lesson notes are only available for subscribers.