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.xlsx11.9 KB Naming Ranges - Completed.xlsx
12.5 KB Naming Ranges - Extra Practice.xlsx
12.8 KB
Quick reference
Topic
Naming ranges.
Description
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 Named Ranges serve a few purposes in Excel. The first is that they can make our formulas a little easier to read and a little easier to audit.
- 00:12 The second is that they give us a nice way to define points that we can jump to in Excel on a
- 00:17 single cell or a block of cells in a worksheet and we can activate with just a couple of clicks.
- 00:22 Another piece that was really useful is before the Excel Table feature was added
- 00:28 it used to be the standard way that we would actually name a data range that we would use in pivot tables or in VLOOKUP formulas. Because
- 00:35 we always knew then that it will be easier to find out whether or not our data was
- 00:40 actually covering what we thought it was. So let's see how these work.
- 00:45 We have a tax rate showing up in D1 and we need to multiply column B by the tax rate to
- 00:51 come up with our tax. Now we could easily do this with a formula where we just said =B4
- 01:00 times D1 and hit Enter
- 01:02 and it would work for us.
- 01:04 But what if we wanted our formula to be a little bit more auditable, a little easier to read rather than
- 01:08 B4 times D1? It would be nice it it said B4 times tax rate.
- 01:12 Well we can certainly do that. We'll click on D1 and up in the top left hand corner here we have the Name Box. So let's highlight D1 there and
- 01:21 say Tax_Rate. I use underscores in place of the space because spaces won't be acceptable there.
- 01:29 So when I hit Enter it's now named this range Tax_Rate. And no matter where I go I can click on this little drop down arrow
- 01:37 and it'll take me right back there. So that's one way to use Named Ranges is we can use them to jump around the worksheet.
- 01:43 The other thing we can do is we can now use it in our formula so instead of B4*D1 we type in
- 01:50 =B4*
- 01:52 and when I start typing now "T"
- 01:55 we get our list of functions and as I keep going T-a-x I now have my intellisense showing me that tax_rate is a viable option. And if I hit tab
- 02:05 it will actually complete it for me and highlight the tax rates cell for me.
- 02:11 When I hit Enter
- 02:12 that tax rate comes up it's the same mathematical value it's just that my
- 02:16 formulas a little bit more easy to read, I can see what's actually happening.
- 02:20 Now the nice thing about this too is that this is treated as an absolute reference so when I drag it all the way down this column
- 02:27 you'll see that I now have B18*Tax_Rate in
- 02:32 row 18. Which is kind of nice as well.
- 02:36 So that's how we use a single cell. What about naming a range of cells? Well why don't we do that here. We'll name our entire data table here.
- 02:46 So I'm going to highlight the entire set and I'm going to go up to the Name Box and put tblData.
- 02:54 It's a good enough name for right now and we'll hit Enter.
- 02:58 So now if I click anywhere
- 03:01 it goes back to telling me what the cell name is that I'm in.
- 03:05 And if I go back to that drop down list I can pick up
- 03:08 tblData and highlight the entire thing. This is handy as a save for things like
- 03:13 a VLOOKUP function maybe we're going to look up, let's look up chest pain.
- 03:20 "chest pain"
- 03:24 in tblData so I can auto complete with this data,
- 03:28 the column index number that I want to find is, let's look for the net charges, that's going to be in column two and we're going to find an exact
- 03:37 match so we're going with false and hit Enter and it comes back with 4014.
- 03:42 Which or 4014.825 which rounds up to 4015 so there we go. So that shows we can actually use these named
- 03:50 ranges which is a lot easier to read and a lot easier to find later when we're trying to debug our formulas.
- 03:58 Now the other thing that's really cool about this, if we're looking at a massive amount of these things maybe they're even on other worksheets
- 04:04 so it's hard to kind of audit them back and forth. The nice thing about the Named Range is that later on when I come back if I have some
- 04:10 random data in this thing and somebody added some new values that are in here.
- 04:14 I can now go back and quickly highlight my tblData even though I'm not in the debugging mode of my VLOOKUP formula and I can see
- 04:21 that there's a row missing. So that's another way that we can actually use this particular tool.
Lesson notes are only available for subscribers.