Locked lesson.
About this lesson
Learn about working with absolute and relative cell referencing, and techniques for copying formulas.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Cell Referencing.xlsx22.6 KB Cell Referencing - Completed.xlsx
23.9 KB Lean More About Cell Referencing
Link
Quick reference
Cell Referencing
Working with absolute and relative cell referencing, and techniques for copying formulas.
When to use
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 CTRL + C to copy, and CTRL + V to paste.
- Use the fill handle to extend formulas across a range.
- Select the range first, enter your formula, then press CTRL + Enter to commit it to multiple cells at once.
Hints & tips
You can toggle the $ signs on a reference when you are creating a formula by pressing F4 when your cursor is next to (or within) a cell reference:
- Pressing F4 once will toggle to absolute columns and absolute rows
- Pressing F4 again will toggle to relative columns and absolute rows
- Pressing F4 again will toggle to absolute columns and relative rows
- Pressing F4 again will toggle to relative columns and relative rows
- Pressing F4 again will restart the process
- 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.