Locked lesson.
About this lesson
Learn about working with absolute and relative cell referencing, and techniques for copying formulas.
Exercise files
Download this lesson’s related exercise files.
Cell Referencing.xlsx15.7 KB Cell Referencing - Completed.xlsx
16.3 KB
Quick reference
Topic
Cell referencing.
Description
Working with absolute and relative cell referencing, and techniques for copying formulas.
Where/when to use the technique
Use to ensure that formulas are targeted at the intended cells when you copy them from one location to another.
Instructions
Relative vs absolute referencing guide
When you copy the formula and your original cell is shown as:
- A1: Both the column and row will change.
- $A1: The column will always point to A, but the row will change.
- A$1: The column will change, but the row will always point to 1.
- $A$1: Both the column and row will remain pointed to A1.
Key points to remember: Ranges are not loyal. You need to pay them (using $ signs) to stay put!
Copying methods
- Right click and choose copy. Select the destination, right click and choose Paste (or PasteSpecial)
- Press Command C to copy, and Command V to paste
- Use the fill handle to extend formulas across a range
- Select the range first, enter your formula, then press Command Enter to commit it to multiple cells at once.
- 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.