Locked lesson.
About this lesson
See absolute and relative cell referencing in practice, and learn about ways to copy and paste formulas.
Exercise files
There are no related exercise files for this lesson.
Quick reference
Cell Referencing - Example
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 Paste Special)
- 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
Locking cell references - keyboard shortcut
To lock cell references using a keyboard shortcut, you must be editing the formula and have a cell reference selected. Then, press F4 (or ⌘-T on a Mac) to cycle through each kind of cell reference: both column and row locked, only row locked, only column locked, neither locked.
Login to download- 00:04 For this demo,
- 00:05 what I'm going to do is I'm going to build an inflationary cost forecast.
- 00:09 Now, this may not be something that you've had to do before, or
- 00:12 may not ever have to do.
- 00:13 But the key thing I want you to recognize that it is going to require a mixture of
- 00:17 absolute and relative references.
- 00:19 And the English versions of the formula that I'm going to use are right here so
- 00:24 you can actually see them in place.
- 00:26 Now the first thing that I'm going to start with here is I want to put in my
- 00:30 individual years across the top.
- 00:32 So we're going to start with 2020, which is going to be pretty easy.
- 00:35 And then we're going to actually move across to get to 2021, 2022, and so forth.
- 00:40 So the way I'm going to start this is I'm going to go and
- 00:44 say equals 2020 and I'm going to add zero.
- 00:47 And when I go and hit Enter, as you would expect, you get 2020.
- 00:51 So that looks pretty good, but here's where the problem starts.
- 00:54 I'm going to go and copy this across to the next cell.
- 00:58 So I'm going to press Ctrl+C, then I'm going to go over here and
- 01:01 press Ctrl+V to paste it in.
- 01:03 And what do we get?
- 01:03 We get 1.
- 01:05 Why?
- 01:06 Well, let's go click in the formula bar and
- 01:08 we can see which cells this formula is actually referring to.
- 01:11 Well, you can see is that it correctly refers to the 1 that it's adding,
- 01:15 that's great.
- 01:16 But unfortunately,
- 01:17 because this was a fully relative reference when we copied it sideways,
- 01:21 this cell moved one cell to the right and I need it to stay pointed at 2020.
- 01:26 So I'm going to go back to the original formula,
- 01:29 I'm going to select cell C6 and what I'm going to do is I'm going to press
- 01:34 the F4 key to toggle between fully relative and fully absolute.
- 01:40 So now I get dollar C dollar 6 where I'm paying, both the column and
- 01:44 the row to stay put.
- 01:45 Will that work?
- 01:47 I believe it will.
- 01:48 Let's see, so I'm going to go and copy this, Ctrl+C.
- 01:52 Come across here, going to put in all these cells, Ctrl+V.
- 01:56 And what you can see is we've got 2020, 2021, 2022.
- 01:59 And if I take a look at this now,
- 02:01 every time I look at the formula while it is anchoring to our locked in 2020 value,
- 02:07 we can see that because I never actually anchored the column or
- 02:11 the second reference here, it is moving across column by column.
- 02:15 Okay, so that's working quite nicely.
- 02:18 All right, the next thing that I need to do is I need to calculate my total cost.
- 02:22 So for my total cost, this is going to be equal to my cost and
- 02:26 I'm going to multiply that by open parenthesis,
- 02:31 one plus, and I need the tax rate.
- 02:34 So I'm going to close my parenthesis and hit Enter.
- 02:37 And that gives me 28,500, which is great.
- 02:41 To copy this one down, I'm going to copy this slightly differently.
- 02:44 What I'm going to do is I'm actually going to move across here until my cursor
- 02:47 turns into this big black plus.
- 02:49 I'm going to left click and drag this down one cell.
- 02:52 And you can see that this now gives me 35,000.
- 02:55 Does that look like the right cost?
- 02:58 Well, the problem is it's supposed to be multiplied by 1 plus the tax rate, but
- 03:02 I've got the same number here.
- 03:03 So if I take a look, again, clicking on the formula,
- 03:06 you'll notice we're multiplying 35,000 times 1 plus an empty cell.
- 03:11 So that's not so good.
- 03:13 So let me just come back up here.
- 03:15 I'm actually going to delete this formula, and I'm going to change this one so
- 03:20 that C8 is pointing to, again, a fully absolute cell, okay?
- 03:24 So again I press F4, I'm going to hit Enter on that.
- 03:28 And now I'm going to copy this formula down again.
- 03:31 going to do it slightly differently again though for you, instead of actually left
- 03:34 clicking and dragging with this big black plus, I'm just going to double-click it,
- 03:38 and that will actually extend it down all the individual rows.
- 03:40 So that looks pretty cool.
- 03:42 Now we've got 39,900 because C14 on this row is
- 03:46 being Multiplied by the tax rate at C8, and
- 03:49 that has been locked in with absolute row and absolute column reference.
- 03:55 Brilliant.
- 03:57 All right, the next piece that I'm going to do here,
- 04:00 is I'm going to write the inflation formula, the ugly formula.
- 04:04 This is the hard one here, okay?
- 04:06 And this is how this actually goes.
- 04:08 We're going to say equals the total cost multiplied by open parenthesis,
- 04:14 one plus the inflation rate of 2%, close the parenthesis.
- 04:20 And then we're going to use this funky little hat icon here,
- 04:23 okay, which is on the North American keyboard.
- 04:26 This is a shift six that will raise us to a power and
- 04:29 then we're going to choose the number of years which in this case is zero.
- 04:34 Now I'm going to go and hit Enter on this one here and it gives me 28,500.
- 04:40 And this makes sense because we're not inflating it because it is the base year
- 04:45 of the scenario.
- 04:46 But here's where the problem comes in.
- 04:49 When I go and drag this one down, we get hashes.
- 04:52 What does that mean?
- 04:53 It means the number is too big to fit in the cell and if I mouse over it you can
- 04:57 see in the tooltip text the number that's actually come out.
- 05:01 Wow, what is going on?
- 05:04 Let's click on the cell and click in the formula.
- 05:07 So I'm multiplying my total cost, that one's fine.
- 05:11 I multiply by 1 plus C8 so my inflation rate is no longer 2%, it's now 14%.
- 05:17 And I'm not multiplying it by or exposing it to the power of zero years,
- 05:23 I'm exposing it to 2020 years.
- 05:25 I wonder why the number is so big, this is plainly not working correctly.
- 05:29 And to fix this we need to actually go through and
- 05:33 work with a mixture of absolute relative references.
- 05:37 So we're going to come back to the original formula and
- 05:40 now we're going to critically look at these things and try and
- 05:42 decide what do we actually need to have happen here.
- 05:45 As we copy down, the value here was moving down.
- 05:49 That's okay, we're happy with that.
- 05:50 So D 13 right now will leave as somewhat rel or as fully relative.
- 05:56 Our inflation rate though, C7, we need this one F4 Locked in hard.
- 06:02 This one, no matter whether we move down rows or
- 06:05 across columns must be pointed at that cell at all times.
- 06:09 But our next, E11, well here's the thing, I want it pointed in this particular cell.
- 06:16 But well let's see, what happens if we actually do this?
- 06:19 Let's go and change this right away to fully absolute and
- 06:22 let's see what happens now.
- 06:24 If we copy this down, let me just left-click and drag this time,
- 06:28 you'll notice that it gives us the same base case as we have for total cost.
- 06:32 So that actually looks okay until we do this, and
- 06:37 now we've got the same values again.
- 06:41 What is going on now?
- 06:42 Well, let's go take a look.
- 06:44 We're picking up the base case here instead of total cost and
- 06:50 we're still stuck on our original year.
- 06:54 So as we copy across, we need this to stay anchored in this cell and
- 06:59 this one to come across this way.
- 07:01 But as we copy down things seem to be working okay.
- 07:06 It's moving down.
- 07:07 So this again is where we need to get into even more challenging references.
- 07:12 So we're going to come back over here and we're going to say, okay,
- 07:17 as far as D13 goes, as I copy down, I need the row to be relative,
- 07:22 so I need 13 to be able to change.
- 07:25 But that column as I copy cross needs to stay put.
- 07:28 So here's what I'm going to do,
- 07:29 I'm going to press F4 which takes me to fully absolute, we can see dollar D$13.
- 07:34 That's not going to work because we need the column to stay put, but
- 07:38 the row to be relative, so I'm going to hit F4 again.
- 07:41 This time it says, okay, we're going to lock in your row.
- 07:44 Well, that's not going to work if I need to copy down.
- 07:47 So let me hit F4 again, and notice that now it's giving me dollar D.
- 07:50 So we're going to lock the column, but the row will be relative.
- 07:54 Fantastic.
- 07:57 We always want the inflation pointed to this cell here.
- 08:01 So this one needs to be absolute for both rows and columns.
- 08:06 But for our E11 here, while we want the row to always stay put, as we copy down,
- 08:11 we don't want that to change, we do need the column to change.
- 08:15 So I'm going to hit F4 on this one again and notice that now we've got E$11.
- 08:20 So let's see what happens now when we drag this one down.
- 08:25 It looks okay.
- 08:26 And when we drag it to the right,
- 08:28 we can see that now things are pointed to the right area.
- 08:32 So this is much, much better.
- 08:33 We're using a mixture of absolute rows with relative columns,
- 08:37 absolute columns with relative rows and absolute absolute, okay?
- 08:41 Now the last thing I want to show you is how you can actually commit all of these
- 08:45 formulas all at once.
- 08:47 So let me just come back here, click this, delete this.
- 08:51 I'm going to go from the perspective of the active
- 08:54 cell here which has got my 28,500 that's in here.
- 08:57 I'm going to actually go and
- 08:59 click inside the formula bar as if I'm writing this formula.
- 09:01 And what I'm going to do now is I'm going to hold down my Control key and
- 09:05 I'm going to press Enter.
- 09:06 And you'll notice that that actually writes that same formula to all of
- 09:10 the cells in the selected area and it observes the correct relative and
- 09:14 absolute referencing as it goes.
- 09:16 So That is Ctrl+Enter to commit your formula to multiple cells at
- 09:20 the same time.
- 09:21 So we've shown you a mix of cell referencing here,
- 09:24 as well as how to copy and paste via Ctrl+C, Ctrl+V.
- 09:27 How to copy using the fill handle to drag, that was the little black plus sign,
- 09:31 as well as hitting or copying into multiple cells using Ctrl+Enter to commit
- 09:35 your formula to multiple cells at the same time.
Lesson notes are only available for subscribers.