Locked lesson.
About this lesson
How to use Range Names in a formula.
Exercise files
Download this lesson’s related exercise files.
Range Names Part 2.xlsx13.6 KB Range Names Part 2.xlsx
14.2 KB
Quick reference
Range Names Part 2
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
- Basics:
- Cell reference replaced by text
- Accessing:
- Formulae, Names
- Locating:
- Name Box
- Considerations:
- Absolute?
- Limitations
- Inability to delete
- multiple names
Use of Range Names in a Formula
- Add a range name to cell D2 (see previous lesson for notes on how to add a range name) i.e. Tax Rate
- Enter the tax rate into that cell i.e. 30%.
- Enter a formula into cell E7 which incorporates the Tax Rate name (=NPAT x TaxRate) and copy this formulae across
- The formula will use the 30% rate that you have entered into the named cell (D2)
- As you can see below if another user clicks on one of the taxation cells they are able to easily read the formulae
- 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.