Naming Ranges

Quick reference

• 00:04 In this video, we're going to use a bit of an advanced trick, to make your formulas
• 00:08 a little bit more auditable, and also a little bit more copyable, so
• 00:12 that they can actually be moved from one place to another, without going sideways.
• 00:17 So you can see here that I have a tax rate.
• 00:20 And if I wanted to go and mark my tax rate and figure out what the tax was for
• 00:25 my burger, I could obviously go and say equals 8.95 times 7%.
• 00:31 The challenge is of course when I go and copy this formula, if I haven't set it up
• 00:36 as an absolute reference, it's now no longer pointing to where it should be.
• 00:40 It's also a little hard to read, maybe, what this formula is actually doing.
• 00:45 So here's a solution for that.
• 00:47 What we can do is we can select this cell.
• 00:49 Now this cell, as you know, has a name, that name is B3.
• 00:53 But we could also give it another name by typing in the Name Box here.
• 00:59 So I'm gonna go with Tax_Rate.
• 01:01 You can't use spaces in this area.
• 01:04 We're gonna hit Enter.
• 01:06 Now, when we click outside the cell and
• 01:08 we click back in, it now inherits the new name.
• 01:11 Although, we could obviously still go and
• 01:14 use this name here and say, let me go to B3, and it will jump me right there.
• 01:19 But likewise, from anywhere in the workbook now, I can click on Tax_Rate and
• 01:24 it will take me to that cell.
• 01:26 Why this is really neat is because I can also now say =8.95*tax and
• 01:34 you can see, the formula icons have a little fx beside them but
• 01:38 my name grain shows up with this little label beside it.
• 01:42 So I'm gonna click on Tax_Rate, say Enter, I now get 0.63.
• 01:47 My formula is more readable, and I can now copy this.
• 01:53 And notice that it treats it like an absolute reference, which is pretty nice.
• 01:59 Now, what if I wanted to actually assign more than one cell into that name?
• 02:05 Well I could do that, not that specific name.
• 02:07 But what we'll do is we'll create a table of sales items here.
• 02:10 So I'm gonna select the whole thing, and we'll call this one SalesItems.
• 02:18 Maybe I'll put an underscore in that too just to make it a little more legible.
• 02:21 You can't use spaces, this is kinda the programmer's space.
• 02:24 So we'll go with Sales_Items, there we go.
• 02:27 So now what I'd like to do is I'd like to write a vlookup statement.
• 02:30 So we're gonna say equals vlookup.
• 02:33 So what am I going to look up?
• 02:34 I'm gonna look up sandwich.
• 02:37 Where am I going to look it up?
• 02:38 I'm going to look it up in my Sales Items table.
• 02:41 So right away this is better than working with the old dollar sign
• 02:45 absolute reference thing because now we can actually read our vlookup and
• 02:49 see what is the sales item that we're where we actually looking it up,
• 02:53 in the sales item table.
• 02:54 Which column would I like to get back, number two.
• 02:57 Do I want an approximate match, should I guess?
• 02:59 Heck no, we want to go with an exact match.
• 03:02 So we'll say, it comes back with 6.95 for sandwich, which is great.
• 03:07 And I can now, again, copy this down.
• 03:10 And it still points to the same place, that is magic.
• 03:14 If I know want to go and
• 03:16 actually work out the taxes on these of course this is just a formula.
• 03:20 So again we say equals this, times.
• 03:23 We can start, well let's click the cell.
• 03:26 It gives us B3 so
• 03:27 we would actually have to type in Tax_Rate if we want to use that.
• 03:32 But not a problem, it still works.
• 03:34 And we can actually use our formulas on top of other formulas as well.
• 03:40 Now, where would you go if you want to modify this?
• 03:42 Well you would actually go up to the Formulas tab and go to Name Manager.
• 03:47 The Name Manager maintains all of the names that are in the workbook itself.
• 03:51 And you can see that Sales_Items shows up with some curly brackets around it and
• 03:56 shows that it's got the sales enterprise tax and what not, a little bit more.
• 04:00 Tax_Rate shows that it has a specific percentage in it.
• 04:05 It also shows us where the Refers To is.
• 04:08 So where's the target of where it is.
• 04:10 And you'll notice that it gives it the worksheet name plus the dollar signs.
• 04:14 And they are indeed absolute references in here.
• 04:17 It also gives us a level of Scope, which is Workbook in this case.
• 04:21 If you wanted to create a new name, you could do that by clicking New.
• 04:24 And you could actually put in a new name here.
• 04:27 I'm not gonna do that.
• 04:28 If you also wanted to delete something, let's say you wanted to delete Tax_Rate,
• 04:33 you could do that by pressing Delete and confirming it and saying Close.
• 04:39 Be aware that when you do that, all of the formulas that use this will now no longer
• 04:43 understand what Tax_Rate actually is.
• 04:45 But we can fix that by, again, going back to the Name Box or
• 04:49 going to Name Manager and saying New.
• 04:52 Let's give a name of Tax_Rate here.
• 04:54 Guess it knew what I wanted.
• 04:55 And the example is B3, we'll say OK, and Close.
• 05:00 And that'll bring our formula back for us.

Lesson notes are only available for subscribers.