Cell Referencing

Quick reference

• 00:04 In this video we're going to look at two things. We're going to look at absolute vs. relative cell referencing and
• 00:10 we are also going to look at different ways that we can copy formulas from one place to another.
• 00:14 What method you use to copy formulas has no bearing on how the formulas
• 00:18 actually work. That's just mechanics but there's definitely different ways
• 00:21 to accomplish the goal. The table we have for our scenario here is: we've got some wage rates down the left hand side and we've got some
• 00:28 hours across the top and we're going to go and build a few different matrix's to actually show how the number of hours multiplied by the
• 00:35 rate per hour actually builds up a table for us. So, something relatively simple.
• 00:40 We're going to start by just multiplying the cell in A5 by the cell in B4 and will hit Enter there.
• 00:49 And to copy it we will go and select it then say copy.
• 00:54 Select the entire area and say paste. And what you can see is we get some numbers that are not exactly stellar.
• 01:03 We've got a whole bunch of hash marks in here basically what those are, is they're telling us that
• 01:08 the numbers are too big to put in the cell. And what we can see is that this is now not
• 01:12 multiplying at 10 by 1 all the time or 15 by 2 and things like that,
• 01:16 it's actually going and multiplying the cell above by the cell beside it. This is what we call relative referencing when we originally wrote our formula
• 01:25 in cell B5 we were multiplying A5 times B4 and as we move it across we can see that the column reference,
• 01:33 we're no longer referring to column A we're referring to the cell beside the one we are at.
• 01:38 The challenge with this is the numbers get so big that it actually causes a number error down on the bottom here because Excel can't figure
• 01:44 out how to multiply numbers this long against each other and it basically runs out of space. So that's not good.
• 01:50 We'll move this back right now.
• 01:52 Now by contrast we can also set up to use absolute referencing and the way we do that is
• 02:00 we essentially go in and we "pay" our rows and columns to stay put. So we do that by
• 02:05 putting dollar signs in front of the column reference and the row reference
• 02:11 and then we can multiply that by a different cell and again we'll put
• 02:16 a dollar sign in front of the column reference and a dollar sign in front of the row reference.
• 02:21 We hit Enter we'll now go and we'll copy this one. This time to copy I'm going to select it and type Ctrl+C.
• 02:28 That's the windows shortcut for copying. We'll put it over our entire range, right there and Ctrl+V
• 02:37 and what you can see is that this time we end up with 10 all over the place.
• 02:41 Well the reason now is because when we're copying across, where this was pointed to A5 or A15
• 02:50 multiplied by B14 what you'll see is when we go in to our next cell, the exact same formula no matter where we are in the table
• 02:57 it's always referring to these top two cells multiplying
• 03:01 against each other. Well that's not really going to help us either.
• 03:03 So now lets try this different. Let's try working with relative columns but absolute rows. So we'll go and we'll set up our formula again,
• 03:11 this is going to be =H5*I4
• 03:15 but this time what we'll do is we'll leave our columns alone but will pay just the rows to stay put.
• 03:23 And see what happens. So it's =H$5*I$4 and we hit
• 03:28 Enter. We still get our 10 and this time we're going to use the fill handle to actually
• 03:32 extend our formula. So when we move over the bottom right hand corner of the cell
• 03:36 we get this black + we're going to left click and drag it across and that will
• 03:40 extend all the formulas. They look good now and I'm going to left click and drag it down.
• 03:46 Well actually, do they look good? Not so much. They're all the same and as it turns out this is working fine but when we get into J5 here we
• 03:55 can see that it's actually still multiplying the cell beside us. Again, not ideal that's not really what we want.
• 04:01 Lets try it now with absolute columns and relative rows so in this case again we'll set up our basic formula.
• 04:07 And then what we're going to do is we're going to go and make just the columns absolute.
• 04:14 And hit Enter and this time we'll use the fill handle to go the other direction first. We'll go down, this doesn't make any difference,
• 04:22 and across.
• 04:25 And again we get the same formula in every column because
• 04:29 when we copy these across, looking at these two cells,
• 04:34 and looking at these two cells, so these are staying in exactly the same spot.
• 04:38 But when the rows move down we're looking at the rows above. So we keep on multiplying by increments that are going here which is where
• 04:44 we get our hash marks because the numbers get too large.
• 04:48 So the ideal way to do this is to actually use mixed referencing where we mix the appropriate column references together.
• 04:55 I'm going to show you one final copying trick and the way that we do this is we actually start by selecting the entire table first,
• 05:01 then we're going to enter our formula.
• 05:03 =O5*P4
• 05:07 and now what we're going to do is we're going to say we want column O. We always want to refer to column O
• 05:12 so we'll pay that to stay where it should be. We're going to leave the row reference though alone.
• 05:17 And we're going to make sure we always stay in row 4 from our red cell here. So we'll put a dollar sign in front of that. And now I've selected this
• 05:25 entire area, I've put in my formula I'm going to hold down my Control key and press Enter.
• 05:31 And what that does, it actually commits the same formula to all cells. So we've got
• 05:36 all of them going in one place and this time you can see that
• 05:40 these are now pointing to the header rows and columns but they change where they need to but they stay put where they need to as well. So
• 05:49 this is where we use a mixture of absolute and relative referencing to make sure that we've got exactly the right information.

Lesson notes are only available for subscribers.