Cell Referencing

Quick reference

• 00:04 In this video, we're going to look at absolute versus relative cell referencing,
• 00:08 and how they can affect our formulas.
• 00:11 What we have here is we have a table that has wage rates down the left-hand side,
• 00:15 and number of hours worked across the top.
• 00:17 And what we'd like to do is build a grid that actually shows us
• 00:21 the wage rate multiplied by the number of hours.
• 00:24 So our first temptation might be to go and say equals ten, times one and hit enter.
• 00:30 And then what we'll do is go and select that cell and say Control C to copy,
• 00:35 we'll select the entire area and say control V to paste it in all cells.
• 00:41 Now, what you can see here is this looks a little bit ugly.
• 00:43 We put a whole bunch of hash marks, and
• 00:45 basically what the concept is here is that these hash marks are showing you
• 00:50 that the number's too big to fit in the cell right now.
• 00:54 And when we actually expand one of these columns,
• 00:57 we can see where the issue starts to roll in here is if I look at these two cells,
• 01:02 I can see that I'm not multiplying 20 by 3.
• 01:05 I'm actually multiplying the cell above by the cell at the left.
• 01:08 And as the values get bigger as we roll through this table, they actually get so
• 01:13 big that Excel actually runs out of the ability to compute them
• 01:16 by the time we get down to the numbers that are so
• 01:19 large here being multiplied against each other so plainly this isn't gonna work.
• 01:23 I'm just gonna set this back.
• 01:25 Plainly this won't work for what we're actually dealing with here.
• 01:28 So we need a way to lock in our cell references so
• 01:32 what I'm gonna do here is I'm gonna now say, you know what?
• 01:35 Let's go with equals the ten.
• 01:38 But I'm going to make dollar signs go in front of the A and the 15.
• 01:44 And this is what we call making an absolute reference.
• 01:48 I'm gonna then multiply this by B14.
• 01:52 And again, I'm going to put dollar signs in front of both the column and the row.
• 01:58 And this will lock these in.
• 02:00 So now what I'm gonna do is I'm going to right-click and
• 02:05 say copy and we'll select the cells we wanna put this in and
• 02:10 we'll right-click and say paste a slightly different format here.
• 02:14 And this doesn't look like it helped us either because when I go down and
• 02:17 I look at this cell it's not locked in to the top left hand corner.
• 02:21 So we're in a different,
• 02:23 but certainly not better situation than we were before with the previous ones.
• 02:27 So let's see if we can do something a little bit different here.
• 02:31 This time we'll try and lock in our rows.
• 02:35 So, we will say equals H5 and we'll lock the row number by paying it to stay put.
• 02:40 We'll say times I4 and we'll lock the row number here by paying it as well.
• 02:47 We've got H$5$ and I$4$ and this should cause the rows to stay absolute.
• 02:54 I'll show you another method to copy to as I right or I mouse over the bottom right
• 02:58 hand corner of the cell the cursor changes to this big black plus.
• 03:01 Now I'm going to left click and drag over to the side and
• 03:05 everything starting to look pretty good.
• 03:06 Now I'm going to left click and drag down.
• 03:10 And it looks like it's not so
• 03:12 good afterall because we're still actually looking at values in the first row.
• 03:17 So not quite so good afterall.
• 03:21 So in this particular case then, we want to go a different way.
• 03:24 Let's try something else here.
• 03:25 Let's go with =10.
• 03:27 This time what we'll do, is we'll lock the column in.
• 03:30 Let's say dollar sign there and
• 03:32 we'll multiply it by I14 and we'll lock the dollar sign in front of the column.
• 03:38 I'll show you one more quick method here too,
• 03:41 if you're feeling down when you mouse over the bottom right hand corner here.
• 03:46 You can double click and it will fill down and then you can move across.
• 03:53 And we can see that this isn't working for us either.
• 03:56 Even though we've locked our columns in, our rows are still relative so
• 04:00 it's not really doing us any favors here.
• 04:03 So that's not good.
• 04:04 What we're going to need to do is we're going to need to do some mixed
• 04:07 referencing.
• 04:08 I'm going to show you one final trick for copying while I do this as well.
• 04:12 Notice that I've selected from the top left cell all the way
• 04:15 down to the bottom right hand corner.
• 04:17 What I'm going to do now is write my formula.
• 04:19 I'm going to say I always want it to point to this column
• 04:22 no matter where in this formula, or where in this area I am.
• 04:26 I'm going to multiply this and say I'm going to leave this column free to
• 04:31 move around, but I want to lock in this row.
• 04:34 So this is what we call mixed referencing,
• 04:35 where we have different referencing styles.
• 04:38 And now what I'm going to do is I'm going to hold down my control key and
• 04:41 I'm going to press Enter.
• 04:43 And what you'll see is that the Control Enter
• 04:46 commits the same formula into every cell that I've selected.
• 04:51 And what you can see as well is when you click inside the cell,
• 04:54 it's now locked to column O.
• 04:57 But the row is relative and changing.
• 05:00 It's also locked to row 4, but the column is relative and changing as well.
• 05:05 So there's five different methods for copying and there's a whole bunch of
• 05:09 different ways that you can mix up your referencing for absolute
• 05:13 versus relative referencing that'll really help you make robust solutions.

Lesson notes are only available for subscribers.