Naming Ranges

Quick reference

• 00:03 In this video, we're going to look at naming ranges in Excel.
• 00:08 Now every cell inside Excel already has a name.
• 00:12 Right now the cell that I've selected here, you can see has a name,
• 00:15 it's called B6.
• 00:17 And if I move around to somewhere else, this one's called C6 and whatnot.
• 00:21 So basically the name right now is the intersect between the column letter and
• 00:25 the row number.
• 00:27 But we can actually go and do different things with that.
• 00:30 And there's some real important reasons why we would wanna make that happen.
• 00:34 If you'll recall, if we were to use something like a VLOOKUP statement
• 00:39 right now to lookup our sandwich against a table of sales items, and
• 00:43 return the second column comma false,
• 00:48 One of the things you'll be aware of is that if we were to copy this formula down,
• 00:52 and a lot of people forget to lock in their formula references, so
• 00:56 once we've actually copied this with a relative reference,
• 01:00 we can see that we're looking up burger in a table that doesn't even have it anymore.
• 01:05 Because our sales have, sort of for our tables, have gone a little bit offline,
• 01:08 because it was never locked in with an absolute value set.
• 01:12 So that's a bit of a challenge that can end up burning a lot of people as
• 01:15 they use VLOOKUP.
• 01:16 One of the interesting things though,
• 01:18 is that even if this were locked down to absolute, this is not extremely readable.
• 01:24 And it would be nicer if we could actually understand what was going
• 01:26 on by looking at the formula, what data we're looking up.
• 01:29 Where are we looking it up?
• 01:31 So for this reason we have the ability to actually name cells.
• 01:34 So I'm gonna start with a single cell first, and
• 01:37 what I'm gonna do is I'm gonna go with this one here.
• 01:40 The way I'm gonna do it is come over to this little area with a name box.
• 01:43 I'm gonna select the name of the cell currently,
• 01:45 and I'm gonna replace it with tax_rate.
• 01:51 And you'll notice now that it has a name of Tax_Rate.
• 01:54 And if I click anywhere here, I can very quickly jump back to that cell by clicking
• 01:58 on the drop down arrow and choosing Tax_Rate, and it'll take me back there.
• 02:03 I can do the exact same thing for a block of cells.
• 02:06 So if I grab this entire set of cells here and call this one Sale_ Items.
• 02:13 I'm using an underscore to emulate a space because you can't actually use a space in
• 02:17 these names.
• 02:18 I'll hit Enter on this, and now you'll see that again, I can go and select any cell.
• 02:24 Click on sales items and it will take me right back there.
• 02:26 In fact, I can even do the same thing from any other worksheet in my workbook.
• 02:32 So if I go back to sales items now, it will go back and
• 02:34 select that set of sales for me.
• 02:36 So this is also a nice little navigation tip that we can use.
• 02:39 Now, let's go back and modify this formula here.
• 02:43 Instead of using A11 to B16, what I'm going to do is I'm going to start typing
• 02:48 the name of my table, and you'll notice that it even shows up in the IntelliSense,
• 02:52 so I can press Tab and it'll complete it for me and it shows nicely.
• 02:57 Enter, and if I copy this formula down now,
• 03:01 you'll notice that the boundaries stay where they're supposed to, because
• 03:05 sales item is treated as an absolute reference, and that's kind of nice.
• 03:09 We could do the same thing, of course, if we wanted to work with tax rate.
• 03:13 Let's say if we wanted to set this up to say equals
• 03:16 6.95 times open brackets one plus.
• 03:21 And we clicked on our individual cell here, where it says tax rate.
• 03:25 And if I hit Enter now, and copy this formula down,
• 03:28 whoops, let's try again over there.
• 03:31 Here you go.
• 03:32 You'll see that now, it's still using tax rate as an absolute reference.
• 03:38 And what's kind of cool here is I can look at this formula and I can say,
• 03:40 well, I'm looking at B8 and I'm multiplying it by 1 plus the tax rate,
• 03:44 well I can actually have a really easy to read formula this way.
• 03:47 Now, to be fair, you can overdue this.
• 03:49 Okay, you don't want to name every cell in your workbook, that's just crazy.
• 03:52 But it is kind of nice to be able to look back at a formula like this and
• 03:56 say I'm gonna look up A8.
• 03:57 What's in A8?
• 03:58 Oh, it's burger.
• 03:59 And I'm gonna look it up on the sales item table, and return the second column.
• 04:02 That's kind of helpful.
• 04:05 Now what if we made a mistake in our name and we wanted to change it?
• 04:08 Well, to do that what we do is we can actually go to, and
• 04:11 we can select any cell to do this, we go to the Formulas tab.
• 04:14 And we go to Define Name.
• 04:17 When you click on that,
• 04:18 you'll notice that you have a listing of the names in the workbook already.
• 04:22 So I can see Sales_Items.
• 04:23 It has the name Sales_Items here.
• 04:25 So I can change it if I wanted to.
• 04:27 And it tells me where it's actually going to.
• 04:30 I can take a look at tax rate, if I wanted to create a new name range,
• 04:34 I could hit the plus icon and I could do that here as well.
• 04:38 If I wanted to remove one, I could actually select the minus button and
• 04:42 make it go away.
• 04:43 So that's how we would end up working with creating new name ranges.
• 04:47 But honestly, the fastest way to do it is just to select your range that you want,
• 04:52 and just do it through the name box right here.
• 04:54 It's really, really quick.
• 04:55 The other thing I should point out is that,
• 04:58 you don't have to refer to the name range here.
• 05:01 Instead, where this is now saying Tax Rate.
• 05:04 It's not like I've completely replaced the name of the existing cell.
• 05:08 If I said B3, it's still gonna actually refer to it correctly.
• 05:11 So we don't lose anything by naming ranges.
• 05:14 In fact, we actually gain from it.

Lesson notes are only available for subscribers.