Naming Ranges

Quick reference

• 00:04 In this video, I want to introduce you to the concept of named ranges in Excel.
• 00:10 Now every cell already has a name.
• 00:13 In this case here I've selected this green cell and the name of it is B5,
• 00:17 it's the name of the intersection of the cell from column to row and
• 00:21 we can see it up here in the name box.
• 00:23 Now what's interesting though is we can click inside the Name box and
• 00:27 select this and we can also go and provide an alternate name.
• 00:31 Now I'm providing the name Tax Rate for the cell because that's what it is.
• 00:35 And you'll notice that I've used an underscore not a space in the name, and
• 00:38 that's because spaces are prohibited.
• 00:40 So the underscore is what we call the programmer space.
• 00:43 When I hit Enter, you'll notice that it seems to commit it, but
• 00:47 what does it actually do?
• 00:48 Here's the thing, if I click outside that cell, I'm in D5 now,
• 00:53 if I click back inside, I'm in cell Tax Rate.
• 00:56 It's actually given a new name.
• 00:58 It's no longer calling it B5, although I can still type B5 and
• 01:02 it will take me directly there.
• 01:05 But the additional bonus component from this is that no matter where I am in my
• 01:09 workbook, if I click the drop-down, I can actually see tax rate and
• 01:13 I can select it and it'll take me right there.
• 01:15 So this gives me an instant navigation ability inside
• 01:19 my workbook if I name key things.
• 01:21 Maybe the home cell in a specific worksheet I could build myself a quick
• 01:25 navigation table of contents from here.
• 01:28 The other cool thing though is that we can use these in formulas.
• 01:31 So if I come down here and I say,
• 01:33 I'd like to calculate what is the tax on my burgers?
• 01:36 The tax is going to be = ROUND because I want to round it to two decimals.
• 01:41 We're going to go with number times the tax.
• 01:45 And as soon as I type in tax, it comes up with tax rates.
• 01:47 So I'm just going to hit tab to commit that and
• 01:49 I'll round it to two decimal places.
• 01:51 And boom, there we go.
• 01:53 The tax is 0.63, which is 8.95 times 7%.
• 01:57 What's neat about this is that these tax rates actually
• 02:02 function as a fully absolute reference.
• 02:05 So B14 is fully references or fully relative.
• 02:09 As I copy that down, it's going to go to B15, B16, but
• 02:13 you'll notice that tax rate does not change.
• 02:16 All the way down, it's still tax rate, so
• 02:19 it's basically acting as a fully absolute formula.
• 02:22 Now, I'm going to name another range here.
• 02:27 I'm actually going to name a range of cells here.
• 02:29 And I'm going to go and call this one here, Salesitems.
• 02:33 I'll just put all that together, okay?
• 02:35 So this is multiple cells in one big name range.
• 02:39 And if you look at this now we can jump to Tax Rate.
• 02:41 We can also jump to Salesitems, it will select all that data.
• 02:45 And this is important if you want to go and start writing some formulas, for
• 02:49 example, let's make this compatible with all versions of Excel.
• 02:52 We're going to use a VLOOKUP to look up sandwich in the Salesitems table.
• 02:59 So I no longer have to use nasty references to go from a $1 something or
• 03:03 other to a C dollar or something.
• 03:05 I don't have to do that we can provide the table name.
• 03:07 We'll ask for the column index number which is going to be 2 and
• 03:11 we'll provide a false for our return on this one.
• 03:15 And there we go.
• 03:17 We've got 695, bring us back the sandwich price.
• 03:19 And again, this is what's really nice about this is I can very easily go and
• 03:24 roll this down because Salesitems stays as a fully absolute reference.
• 03:29 So it doesn't shift when I'm copying it from one area to another.
• 03:32 All right, so let's use this again.
• 03:33 I'm going to grab the included tax cell here,
• 03:36 and we're going to write a nice little formula, which is = ROUND.
• 03:39 We're going to grab our sandwich in B8 and we're going to multiply that by one plus,
• 03:45 and I'm just going to type in tax and hit tab to complete tax rate.
• 03:50 Comma 2 for the number of decimals I want, hit Enter.
• 03:53 And we have $7.44, which is the tax in price for sandwich.
• 03:58 I'm going to drag this down, you can see that again because tax rate functions is
• 04:02 like an absolute reference it shows up.
• 04:04 And as a bonus helps make my formula much more readable than seeing
• 04:09 a reference to dollar B, dollar five.
• 04:11 So that's pretty cool.
• 04:12 Now once you have these named ranges, though,
• 04:15 you're going to want to know where you manage them.
• 04:17 Where do you change them or edit them?
• 04:18 And you can do that via the Name Manager on the Formulas tab.
• 04:22 Notice that all the named ranges in the workbook are listed here.
• 04:26 We could grab one, we could create a new one, we could edit one.
• 04:29 So we could change the name or change the cell that is pointing to,
• 04:34 or we can also delete named ranges.
• 04:36 And I'm going to do this to show you what happens when we delete
• 04:40 a used named range is you'll get this #NAME?error.
• 04:44 And this indicates that a name has been defined that is no longer in the workbook.
• 04:48 Or it indicates that somebody has built a formula in a newer version of Excel like
• 04:53 XLOOKUP and then sent it back to a version of Excel that doesn't have it.
• 04:58 Excel 2016 wouldn't know what XLOOKUP was and we're returning the same thing.
• 05:02 In this case, fortunately,
• 05:04 it's really easy to fix I'm just going to go back to cell B5 type in Tax_Rate,
• 05:09 rename the cell, and boom everything is going to work quite nicely.
• 05:13 So this is a super useful feature but
• 05:15 I hope that you'll actually explore in some of your more complex workbooks.

Lesson notes are only available for subscribers.