Cell Referencing - Theory

Quick reference

• 00:04 One of the really important things to master when you're working with Excel
• 00:08 formulas is cell referencing.
• 00:09 And there are four particular states that this can take.
• 00:12 So we need to walk through and show you what these are.
• 00:14 The first one is what we call fully relative.
• 00:17 So if you take a look at the spreadsheet image that I have here, and
• 00:20 we decide to put a formula inside B1 that says equals A1,
• 00:23 we can pretty much come across confidently and say, hey, you know what?
• 00:27 That's going to equal 10 because it's at the intersection of column A and row 1.
• 00:33 And that's typically the way that we'll read a formula like this to say,
• 00:37 what cell is it going to give me.
• 00:38 The challenge here though, when you start copying this formula round,
• 00:42 it's no longer going to point to cell A1.
• 00:45 So in truth, what we're seeing here is when we write this formula,
• 00:49 we're not actually really writing a form that says equals A1.
• 00:52 Instead, what we're doing is we're writing a formula that says,
• 00:57 give me the cell position relative to where I started from.
• 01:00 In this particular case, I want the cell one cell to the left of where I am.
• 01:07 So always when we're looking at a relative reference,
• 01:09 we actually face it this way when we're trying to figure out what it means is
• 01:13 we look at the position of the individual cell, and we try and
• 01:16 work out what are the offsets to the left, to the right, up or down.
• 01:19 So in this case, we actually have a formula that says,
• 01:22 give me the cell one cell to the left of B1.
• 01:25 And that's what we call a fully relative reference.
• 01:28 Why this is super important is because what happens when we start actually
• 01:32 copying these formulas around?
• 01:34 So in this case, I'm going to copy the formula in cell B1, and
• 01:38 I'm going to put it into C1.
• 01:40 And what I get is I get equals B1, which is one cell to the left.
• 01:46 Now that cell happens to equal A1, one cell to the left, which has a value of 10,
• 01:51 so it's still going to flow through and be 10 in cell C1 as well.
• 01:55 If I copy that one cell to the right again, look at that.
• 01:58 It's still equals one cell to the left, so D1 now equals C1.
• 02:02 Okay, so every time we copy it,
• 02:05 it's now giving us a cell position relative to what it was previously.
• 02:09 Copying down, same thing happens, one cell to the left,
• 02:12 this time the row number is changing.
• 02:14 And again, the row number changes when we move it down.
• 02:17 The same thing happens if we copy down and right.
• 02:20 You'll notice that C2 gets the formula of equals B2.
• 02:24 And it doesn't matter which of the other formulas on this particular spreadsheet
• 02:28 image I've given you, you copy, they're all the same.
• 02:30 It's going to copy from the position of wherever the cell was that you started.
• 02:35 And there we go in D3, we would actually get equals C3, okay?
• 02:39 So this is fully relative referencing.
• 02:43 This is slightly different than what we call fully absolute referencing.
• 02:47 In fully absolute referencing, we write a formula it looks like this, =$A$1.
• 02:53 Now this one with the dollar signs there, actually really does point to A1.
• 02:58 It will actually preserve its position when you copy it,
• 03:02 very different from a fully relative formula.
• 03:05 So if we go and copy to the right, it's still going to equal A1.
• 03:09 It's still going to equal A1 if we put it in D1.
• 03:12 And if we start copying down, same thing happens, the positions never changed,
• 03:17 down to the right same thing, it's always pointed at A1.
• 03:21 The big difference here is we've done what we call anchoring references, and
• 03:25 we actually do that by putting dollar signs in
• 03:28 front of the components that we want to lock in place.
• 03:31 And if you can take a look at the top here you can see I've highlighted the dollar
• 03:35 signs in front of both A and 1.
• 03:37 I always joke with this that ranges are a little bit disloyal,
• 03:39 when you copy them around they'll move.
• 03:41 If you want to get them to stay put, just like people,
• 03:44 they respond to financial incentives, pay them.
• 03:47 Pay them twice, and they'll really stick around, okay?
• 03:50 So it's a big thing there.
• 03:52 One thing just for international audience you need to know, ranges like dollars.
• 03:56 They don't like euros, they don't like British pounds or yen or
• 04:00 anything like that.
• 04:01 So it's always the dollar sign no matter what language you're actually working in.
• 04:05 Now, you've seen fully relative, you've seen fully absolute,
• 04:11 we can also get into an area where we actually mix these up.
• 04:15 So if our base formula looks like this, =A$1,
• 04:19 notice that we only have a single dollar sign in this,
• 04:22 and it's in front of the 1, but there's no dollar sign in front of the A.
• 04:27 The reason this is important is because at this point our column is
• 04:32 actually relative, but the row is absolute.
• 04:35 And that means that this formula, if you actually break it down,
• 04:40 will always give me row one of the column to the left.
• 04:43 So if I start copying it down, you'll notice that it still points to A1,
• 04:48 because we haven't changed the column.
• 04:50 Even though we went to a new row, though, the row's been locked and
• 04:53 it's absolute for row 1.
• 04:55 Copy down again, same thing.
• 04:57 But when we start copying it across, again, the column is relative.
• 05:02 So, at that point it's going to change from A to B when we copy it from B to C.
• 05:07 Once again, if we come across to D you'll notice that in D1 formula
• 05:12 would now be =C$1, because that column is relative.
• 05:16 And if we copy it through the entire thing,
• 05:19 you can see that it is always going to refer to row one of the cell to the left.
• 05:25 Now, what if we switch this up?
• 05:28 What if we put our dollar sign in front of the A instead, and
• 05:31 we leave the one relative, but now we've got an absolute column?
• 05:36 As we start going through and copying this one across now,
• 05:40 it's always going to refer to the same row of column A.
• 05:43 Why the same row?
• 05:44 Because we're on row 1 when we wrote the formula, and we pointed to row 1,
• 05:48 so that's the same row.
• 05:50 Okay, so as we go across here, you can see that the column is not going to change.
• 05:55 And the row number doesn't change because we haven't switched rows.
• 05:58 But if we go and start copying it down, that row number is going to
• 06:02 be relative to the row that we've actually put the formula in,
• 06:05 where the column stays the same.
• 06:07 And this is the important thing to recognize is that you've got four
• 06:10 different states that you can deal with, fully relative, fully absolute,
• 06:14 where we pay both the row and the column to stay put.
• 06:17 And then we've got these mixed references where we've got a relative
• 06:22 column with an absolute row, or a relative row with an absolute column.
• 06:27 The other important thing to be aware of with this is that when you start writing
• 06:31 more complex formulas,
• 06:32 every single cell reference in there can be treated differently.
• 06:36 So in the case of what you see on screen now,
• 06:38 we've got our first reference with an absolute column and relative row.
• 06:41 The second one has a relative column with an absolute row, and
• 06:45 the final one is fully absolute.
• 06:47 And this is a completely valid formula.
• 06:51 One of the things you'll want to know when you actually start working with your
• 06:54 formulas, is that as you're writing them, you can press F4 to actually toggle
• 06:58 through the different relative and absolute states.
• 07:01 And if you press it five times, it'll actually start back over at the beginning,
• 07:04 so you can just keep on cycling through.

Lesson notes are only available for subscribers.