Locked lesson.
About this lesson
How to use Range Names in a formula, continued.
Exercise files
Download this lesson’s related exercise files.
Range Names Part 3.xlsx13.8 KB Range Names Part 3.xlsx
14.2 KB
Quick reference
Range Names Part 3
Discover how to use a range names in a formula.
When to use
A range name is used to replace a cell reference with text. In spreadsheets, used appropriately and sparingly, great value can be obtained from using range names, as it can make formulae easier to read.
Instructions
- Highlighting cells C4:F8 and then employing the keyboard shortcut CTRL + SHIFT + F3 would generate the following dialog box:
- Highlighting C4:F8 and using the keyboard shortcut CTRL + SHIFT + F3 once more should generate the Create Names dialog box as above with both ‘Top row’ and ‘Left column’ checked. This means that D4:D8 will be called ‘Apples’, E4:E8 will be called ‘Oranges' and so on. This would take considerably longer to perform manually.
- This example also reinforces why spaces are illegal characters in range names (and for that matter, should not be added to formulae either). Space is the intersect operator in Excel. If you were to type the following formula:
=Oranges Summer,
- Excel would return the value in cell E6 (the intersection of the two ranges, above), i.e. $180. This can be a powerful yet quick and simple analytical tool for key outputs.
Login to download
- 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.