Locked lesson.
About this lesson
Learn about creating named ranges, and the benefits of doing so.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Naming Ranges.xlsx22.3 KB Naming Ranges - Completed.xlsx
22.5 KB
Quick reference
Naming Ranges
Creating named ranges, and their benefits.
When to use
Named ranges help make formulas easier to audit, give us quick ways to select key data, and can be useful for identifying ranges used for PivotTables, charts and more.
Instructions
Benefits of naming ranges
- Gives us a quick way to select individual or groups of important cells
- Allows us to quickly identify which cells are used in a formula, PivotTable, or chart
- Can make our formulas easier to read
Naming individual ranges
- Select the cell
- Type the name (for example “Tax_Rate”) in the Name box
- You can now select “Tax_Rate” from the name box and be taken immediately to that cell
- You can also use “Tax_Rate” in formulas (i.e. = A2*Tax_Rate)
Naming groups of cells as a range
- Select the cells
- Type the name (for example “Data_Table”) in the Name box
- You can now select “Data_Table” from the name box and be taken immediately to that cell
- You can also use “Data_Table” in formulas (i.e. =VLOOKUP(Tax_Rate,Data_Table,2,FALSE)
Modifying named ranges
- To modify or delete a named range, go to the Formulas tab and choose “Name Manager”
- Select your named range and click Edit or Delete
- 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.