Range Names Part 2

Quick reference

• 00:04 In this second part of three on Range Names, we're gonna look a little
• 00:08 more on what you can do with range names and why they can be useful.
• 00:14 I've already covered the basics so
• 00:15 let's look at some of the more sophisticated things we can do and
• 00:19 some of the things we need to look out for when creating range names in Excel.
• 00:24 Now you might be thinking,
• 00:26 why am I spending three whole sessions on range names?
• 00:30 I get the idea, Liam.
• 00:31 Yeah, move on.
• 00:32 Well, they're quite useful if they're used sparingly.
• 00:37 Let me give you an example with this first range name example here.
• 00:40 Well, I have got my net profit before tax in row six.
• 00:44 I have got my net profit after tax in row eight and
• 00:47 I need to calculate my tax in row seven.
• 00:50 Using the tax rate, which is here in cell D to 30%.
• 00:54 So I can just go down here and go equals minus the actual net profit before tax and
• 01:01 multiply that by the actual tax rate which I cannot call absolute of D2.
• 01:08 And then copy that across using the normal sort of Excel techniques.
• 01:14 Very, very easy.
• 01:15 One consistent formula, n o hard code.
• 01:18 That's how we calculate tax.
• 01:20 That could be one way of doing it.
• 01:23 But what if I went back to this cell here and
• 01:27 decided to call it Tax_Rate?
• 01:30 Now, you can't space In arranged name.
• 01:34 Space is a legal character.
• 01:37 And the reason for this is quite interesting actually.
• 01:40 If I went here went equals, this column space that row.
• 01:46 You might think what on earth does that do?
• 01:50 What actually gets an ounce of, believe it or not, of minus 60?
• 01:56 If I press the F2 function key you'll see the Int set operator is actually space.
• 02:02 This is why you shouldn't put random spaces in Excel formulas in Excel and
• 02:07 why we don't and
• 02:08 why we frown upon the fact that Microsoft Help puts them in everywhere.
• 02:12 Space is the intersect operator.
• 02:15 It can't be used. And can't be used in a range name too.
• 02:17 You can't give a range name a cell reference either.
• 02:23 So you can't call that cell E15 or like that, because that would be confusing and
• 02:27 everybody would like think don't get that either.
• 02:30 We have to keep it nice and simple.
• 02:34 This can cause problems for shorter range names.
• 02:37 One issue I've seen in the real world is somebody trying to call an actual value in
• 02:41 a cell day 1.
• 02:43 But the trouble is, day one is an actual cell reference in excel.
• 02:47 Let me demonstrate, Day1, Cell Day1.
• 02:52 So be careful with short arrange names they might not work.
• 02:56 Okay, I have called this tax right now, I am gonna go back here and
• 03:01 instead of D2 there, I'm going to type in t a x as tax_rate.
• 03:06 Press enter, copy that clause.
• 03:10 Makes it easier.
• 03:11 You see?
• 03:12 That's an actual easier format to follow.
• 03:14 Range names can be a godsend in this particular scenario.
• 03:19 Now, if I've actually used D2 all over the place,
• 03:22 I might forget all the places I've actually done it.
• 03:25 So there's another thing I can do.
• 03:27 I can go to the formula's tab, and rather than click on define name,
• 03:31 I press the drop down box beside it.
• 03:33 I'm going to apply names.
• 03:35 Now the only range name I've got right now is tax range.
• 03:38 So I'm going to apply that and click OK.
• 03:40 And look at that.
• 03:42 It's automatically updated wherever it's found the cell D2 reference.
• 03:46 That's great, right.
• 03:50 So, looking good.
• 03:52 But be careful.
• 03:53 Let's look at the second example.
• 03:55 What I've got here is a varying tax rate.
• 03:58 So here in years one through Year 5, I've actually got 30% tax rate in the 1st year,
• 04:03 moving up to 50% in the final year.
• 04:08 Now if I actually highlight all of this, and call it I haven't got enough room.
• 04:12 You can widen it since Excel 2007 came out, so we gonna widen the name box and
• 04:18 call it Varying_Tax_Rate.
• 04:25 Check it's there.
• 04:25 Always check it's there because if you don't press enter carefully enough,
• 04:28 it might not come up in the name box.
• 04:29 I can go equals minus.
• 04:31 The actual net profit before tax multiplied by the varying tax rate.
• 04:38 Now little tip for you, if you are a keyboard user, you go down here,
• 04:43 you press Enter, this is what happens.
• 04:46 You get the hash name error, why?
• 04:49 Because it just went and entered what you typed.
• 04:53 You got to be careful.
• 04:56 You got to press the tab button first to get it in there, then press Enter,
• 05:00 then it will work.
• 05:02 Now, notice what just happened here.
• 05:06 Not only has it actually taken the tax rate, it's taken it from the same period,
• 05:13 filter column F, do you see the tax rate in column F is 35%?
• 05:18 And 35% of 200 is 70.
• 05:21 Here I've got 45% of 400 is 180.
• 05:25 When you've got range name that consists of more than one cell, it takes
• 05:30 the value in the corresponding row or column that the two references share.
• 05:35 So just be careful.
• 05:37 Cuz that means if I actually take this and
• 05:39 bring it out one more period, I'll get a hash value error here.
• 05:43 Because there is no value here for this cell,
• 05:46 the actual range length does not extend that far, so be careful.
• 05:51 One of the classic gotchas, more in the next session.

Lesson notes are only available for subscribers.