Naming Ranges

Quick reference

• 00:04 In this video, we're gonna use a bit more of an advanced trick to make your formulas
• 00:09 a little bit more auditable, a little bit more readable,
• 00:12 and also a little bit more stable.
• 00:15 Now, you'll notice here that I have a cell that contains a tax rate.
• 00:19 And that's pretty useful, but if I wanted to go and say multiply by $8.95 times
• 00:26 my tax rate of B5 and hit Enter, that works.
• 00:30 But of course if I forgotten to actually mark this as an absolute reference,
• 00:34 when I copy it down it's not gonna do what it's supposed to do.
• 00:37 And we can see that very quickly, it will do some strange things,
• 00:43 now this cell, as it happens, has a name and that name is B5.
• 00:48 But the interesting thing is, you can see when I mouse over this,
• 00:51 this is a name box and I can actually come in here and give the cell another name.
• 00:56 I could call this one tax_rate.
• 00:59 Now, why the underscore?
• 01:01 Because you can't use spaces in this box and when I hit enter.
• 01:05 What you'll see is that I now have a cell name called Tax Rate.
• 01:08 If I click outside, I can see D5, which is the cell that I'd selected,
• 01:13 and I can go and choose Tax Rate, and it'll jump me right to it.
• 01:17 Now, of course, I can also go and type in B5 and
• 01:21 I can jump straight to that cell as well, so it now has two names.
• 01:25 Why this is useful to me though?
• 01:27 Is because I can now go and say, equals this times, and
• 01:31 I can start actually typing in Tax Rate, and tab.
• 01:36 And now you can see that my B14 is being multiplied by my Tax Rate.
• 01:40 And even better when I go through and I drag this down,
• 01:44 you'll notice that it works because this is always referring to the Tax Rate.
• 01:47 This is like an absolute reference but one is named nicely.
• 01:52 Where did this get even better?
• 01:53 Well, it gets even better when we take something a table like this and
• 01:57 we come back and say, I really like to name this Sales Items.
• 02:03 Now I have a name for this as well.
• 02:05 So I can jump to my Tax Rate, or to my Sales Items.
• 02:10 Why is this important?
• 02:12 Well it's important because, when I want to go and
• 02:15 write something like a VLOOKUP statement.
• 02:19 What do I wanna look up?
• 02:20 I wanna look up sandwich.
• 02:22 Where do I wanna look it up?
• 02:24 I wanna look it up in Sales Items.
• 02:26 Now this is much nicer than going in and typing in a hard coded,
• 02:30 with all those dollar signs in there, to get my range from A13 down to C18.
• 02:36 I can actually read what's going on here.
• 02:38 Which column do I want to pull back?
• 02:39 Number two and, do I want an approximate or an exact match?
• 02:42 Well, I want an exact match because I'm looking for a specific price for
• 02:47 a specific item.
• 02:49 Now I can go and close my parenthesis and hit Enter, and
• 02:52 what you'll see is that as soon as I go and drag this down,
• 02:57 everything works nicely and speaking up to right elements which is great.
• 03:01 So, this is pretty nice because we actually have the ability to go
• 03:04 through and actually name our formulas a lot nicer.
• 03:08 I can come back on here now, and
• 03:12 I could say = this * 1 plus the Tax Rate.
• 03:18 You'll notice that it actually comes up as soon as I go and click on the cell.
• 03:22 Post and hit enter and, now and they're going to fill this down.
• 03:26 Everything is working quite nicely.
• 03:27 So, this is some of the nice beautiful benefits of named ranges.
• 03:32 Now I should also show you where you can find out how to manage this because this
• 03:36 name box is a place where you can go to create names.
• 03:39 But you can't change them here.
• 03:42 But if you go to the formulas tab, you'll notice that we have the name manager.
• 03:46 And when you go to name manager,
• 03:47 it actually brings up a list of all of the individual names that are in the Workbook.
• 03:51 So I could go to sales items and I could say edit and this would allow me to go
• 03:55 back if I need to and change, the basis of where this name actually is and
• 04:00 this is important because names don't automatically expand.
• 04:03 So if I added the new item down the bottom here I would need to come back here and
• 04:07 actually change it in this area to make sure that it gets pulled into the range.
• 04:11 Although, if I inserted a new row in the middle, it would be fine.
• 04:14 Let me go and cancel that.
• 04:16 If I wanted to look at my tax rate for example, maybe it needs to be pointed
• 04:20 to a new sheet, I could easily come back here and change it as well.
• 04:24 So this is how we would go through and actually manage names.
• 04:27 We can even create new ones here or delete them if we should need to do so.
• 04:31 It gives us a little preview of what's actually showing in there, and
• 04:33 even tells us what area this actually refers to.
• 04:37 So names are a very useful thing for working with Excel.
• 04:41 And you're gonna see as we move through the rest of the modules in the course that
• 04:45 they do come up every now and then, because they're kinda like the secret
• 04:48 sauce that allows us to get certain things done.

Lesson notes are only available for subscribers.