Cell Referencing - Example

Quick reference

• 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.