Cell Referencing

Quick reference

• 00:04 In this video we're gonna look at absolute and relative cell referencing and
• 00:08 how they can change your formulas.
• 00:10 We're working with a little table that has wage rates down the left hand side and
• 00:14 hours across the top,
• 00:15 and we'd like to multiply these together to come up with a little grid matrix.
• 00:19 Your first temptation might be to do this, we could say =10 times 1, so A7 times B6.
• 00:28 We'll hit Enter, and then we'll go right-click, and we'll say Copy, and
• 00:33 we'll come down here and right-click, and we'll say Paste.
• 00:37 And you'll notice that we do get a grid, but something's not right here.
• 00:40 10 times 1 is definitely 10, and 2 times 10 is 20, but
• 00:44 3 times 10 is most definitely not 60.
• 00:47 What's actually happening, we wrote the formula from the perspective of this cell.
• 00:51 And we said A7 times B6, but the way that Excel interpreted that is
• 00:56 actually 1 cell to the left times 1 cell above, and
• 01:00 you can actually see that happening right here.
• 01:04 As it's been copied across and down, this is what we call a fully relative formula,
• 01:09 it's always looking 1 cell to the left and 1 cell above.
• 01:12 When we get into a cell that looks like this with hash marks everywhere,
• 01:16 that's simply Excel saying, hey the number's too big to be shown in the cell.
• 01:20 And when we get down to #NUM, that means the number's to big for Excel to
• 01:23 calculate, because it's actually starting to multiply billions against billions.
• 01:28 So, let's press Ctrl+Z and undo that formatting, and let's come down and
• 01:32 see if we can do something a little bit better.
• 01:35 Let's go and multiply our 10, but this time, we're going to press the F4 key and
• 01:40 notice the formula changes to $A$17, that means, a fully absolute cell.
• 01:48 Let's multiply that by B16 and also make that fully absolute.
• 01:55 We'll hit Enter, and it looks like it's working with 10, I'm now gonna say Ctrl+C.
• 02:00 We're gonna highlight this entire area and
• 02:02 press Ctrl+V which is the keyboard shortcut to paste.
• 02:06 Now this time we get 10 everywhere, why?
• 02:09 Well let's go and take a look at this guy here, A17 times B16,
• 02:14 this is why we call this fully absolute.
• 02:17 No matter where we copy the formula, it's still pointing to the original pieces, and
• 02:22 the secret behind this is the dollar signs.
• 02:24 You're paying your rows and your column references to stay put when you copy it,
• 02:30 but that's obviously not gonna work for what we need.
• 02:32 So let's go and try this one,
• 02:35 let's go and say =H7, and this time we'll change it using F4, but
• 02:40 we're gonna change it one more time, so I've pressed F4 again.
• 02:44 This is actually a toggle that allows you to cycle through the different relative
• 02:47 and absolute states.
• 02:48 So we'll now go and we'll press F4 again,
• 02:52 and again so that we have now got locked down rows in both cases.
• 02:56 And what I'm gonna do now is I'm gonna go and copy by using the fill handle.
• 03:01 Notice when I move my mouse over the bottom right hand corner of the cell I get
• 03:04 this big black plus, and if I left-click and drag, it gives me 10 all the way down.
• 03:10 So something seems amiss here already, but
• 03:12 let's left-click and drag this to the right and see what we've got.
• 03:17 So what's happening now, well the challenge here, if we look, is that it's
• 03:22 kept pointing to this particular row even when I've copied it down one row.
• 03:27 And yet when I move across, my absolute row for column J is perfectly fine,
• 03:32 but this guy has moved across with me as well so this isn't working either.
• 03:39 Let's try another, in this one,
• 03:43 we'll say H17, we'll press F4 three times, we'll lock it to the column.
• 03:49 Times, and we'll now go and
• 03:51 again press F4 three times to lock it to column I as well for Row 16.
• 03:56 And now, if we go and we fill this down, let's double click on that fill handle.
• 04:04 Notice that it extends down nicely, and when we drag it across,
• 04:09 well we've got the same numbers everywhere, but is it really what we want?
• 04:13 When we take a look at this guy here, no, it's not,
• 04:16 it's keeping the column in good state.
• 04:18 It means that it can move the column around as needed for one of them, but
• 04:22 it's also not working the way that we want either.
• 04:24 So the challenge is we need this one to stay on the correct row, but allow
• 04:29 the columns to move, and this is where we need something called a mixed reference.
• 04:32 I'm gonna show you one final way to make this work.
• 04:35 Here's what we're gonna do, we're gonna say equals, we're gonna go grab our
• 04:40 10 here, but this time we're gonna press F4 once, twice, three times.
• 04:45 We're gonna say, no matter where in this block I copy this,
• 04:48 I want you to always point to column O, so let's pay column O to stay put.
• 04:53 Times, no matter where in this block, I'm gonna copy this formula.
• 04:57 I want it to point to row 6, but if I'm over here,
• 05:00 I want the column to be able to change.
• 05:02 So we'll go F4, F4 and we'll change it, so we have $O7 times P$6,
• 05:09 Enter, and now I wanna extend this formula over this entire range.
• 05:13 So what I'm gonna do, I'm gonna select it all, press F2, I'm gonna hold down
• 05:18 the Control key and press Enter, and that will commit the same formula everywhere.
• 05:22 And what you see now, is that you've seen five different ways to copy, and
• 05:27 you now have reference that's pointing to the right place.
• 05:30 No matter where in the table you go by properly leveraging absolute and
• 05:34 relative cell referencing.
• 05:36 Pay the rows, pay the columns to stay put when you copy them.

Lesson notes are only available for subscribers.