Range Names Part 3

Quick reference

• 00:04 And now for our final session on range names in financial modeling preparation.
• 00:11 I've covered so far the basics, in accessing, and
• 00:14 I've looked at some of the limitations.
• 00:17 I'm being careful to delete multiple names.
• 00:20 You have to use the Name Manager in Excel 2007, or later.
• 00:24 I want to look now at a couple of other considerations,
• 00:28 looking at some of the limitations, some of the cool tips, and
• 00:31 what happens if you don't follow the absolute references.
• 00:35 Let's have a look at two last examples.
• 00:38 They say oranges are not the only fruit, and they're right.
• 00:41 I've bought apples and lemons as well, in this example, and I'm looking at it for
• 00:46 all four seasons, spring, summer, can just about say fall,
• 00:50 when you're an Englishman, autumn, to the rest of us, and winter.
• 00:54 I have got a little table of data here.
• 00:58 Now I can actually select all that data quite easily by clicking on any cell,
• 01:02 we'll talk about keyboard shortcuts later in the course, but for
• 01:07 now, control A will set that range.
• 01:11 Let me show you something cool you can do with names.
• 01:14 Back on the Formulas tab of the ribbon, in the Define Names section, there's this
• 01:19 thing here, create from Selection, keyboard shortcut, Control+Shift+F3.
• 01:25 This is pretty nifty.
• 01:29 Notice here it's recognized as a table, and it thinks
• 01:32 there are labels in both the top row and the left column, and it's guessed right.
• 01:36 Now Excel doesn't always guess correctly, so you do need to check, but
• 01:40 I want that, and I click OK.
• 01:44 Wasn't that amazing?
• 01:45 You're probably thinking, no.
• 01:49 What have I actually done?
• 01:51 Well, look, if I go to the Name box here,
• 01:53 I've suddenly got Apples Fall, lemons, everything's in alphabetical order so
• 01:58 it's mixed up with tax rate and varying tax rate that we did last time out.
• 02:03 But look if I highlight apples, you see it highlights the column of sales for apples.
• 02:11 If I do lemons, I get lemons.
• 02:16 If I do spring.
• 02:19 Summer.
• 02:21 See what's going on?
• 02:23 I want my summer sales of oranges.
• 02:26 Want to know what I got.
• 02:28 I could use some complicated some ifs function, v lookup, h lookup,
• 02:32 lookup index match, you might not know what any of them are, doesn't matter.
• 02:36 We're talking about range names here.
• 02:37 I mentioned the intersect operator, space before,
• 02:43 and why you can't use it in arrange name.
• 02:48 Here's why, Equals,Oranges, space, Summer, enter.
• 02:53 How difficult is that?
• 02:57 That blows me a way with it's simplicity.
• 03:01 It's not particularly flexible that you can't use drop-down boxes to change it,
• 03:05 but if you just want to get the answer nice and simple.
• 03:07 Well, if somebody landed here from Mars and never seen Excel before,
• 03:11 I'm pretty sure they'd work out what that was doing quite quickly, you'd agree.
• 03:15 Pretty cool, I think.
• 03:19 Final example, here I've got some sales for years one to seven.
• 03:24 Let's do something really exciting, first of all.
• 03:28 Highlight this Cltrl+F3, so it's a different way.
• 03:32 New, and I'm going to call it, not Sales,
• 03:35 I'm going to call it Current_Period_Sales.
• 03:40 Add C4 to I4, just check it's right.
• 03:46 Yep, got it all, click OK.
• 03:48 Wonderful.
• 03:49 Now another thing I want to do here, is I want to actually create last year's sales.
• 03:55 And I do this through a method that might not seem obvious immediately.
• 03:59 What I'm going to do is I'm going to click on this cell, first of all, cell D5.
• 04:02 It's important I do that first.
• 04:05 Then I go to define name, and I'm going to type in here last underscore
• 04:12 period underscore row underscore above.
• 04:18 And I'm actually going to get rid of the dollar signs around D5.
• 04:23 So it's a relative reference, and I don't want it to be D5.
• 04:27 I want it to be the row above, and one cell to the left,
• 04:30 C4, my favorite explosive cell.
• 04:33 So that's the year one sales, the 100, and click OK.
• 04:37 If I actually type in here then,
• 04:42 = that sits there.
• 04:47 I've now got the sales one period to the left or above.
• 04:54 That's fantastic.
• 04:56 What do I use that for?
• 04:58 Well, for transparent formulas.
• 05:00 How about we do E1 near growth?
• 05:03 I can just write now =
• 05:06 Current_Period_Sales/Last_Period_Row_Above
• 05:16 and subtract one.
• 05:19 There it is, I'm going to go to the Home tab, turn it into percentage.
• 05:25 Copy it across.
• 05:26 And I can see the increase in sales each period.
• 05:31 Isn't that a nice and transparent formula using lane change?
• 05:36 The current period sales absolute references.
• 05:39 That last period well above are relative cell reference.
• 05:42 Useful when used sparingly and people follow it.

Lesson notes are only available for subscribers.