Locked lesson.

Upgrade

About this lesson

How to effectively assign and use range names in an Excel worksheet.

Exercise files

Download this lesson’s related exercise files.

Range Names Part 1.xlsx
7.5 KB Range Names Part 1 - Solution.xlsx
8 KB

Quick reference

Range Names

Discover how to add and delete range names.

When to use

A range name is used to replace a cell reference with text.  In spreadsheets, used appropriately and sparingly, great value can be obtained from using range names, as it can make formulae easier to read. 

Instructions

Adding Range Names

  • There are various ways range names may be created in Excel.  One way is to use the Name Box:
     

  • Once you have decided upon a name for your range, perhaps the quickest way of all to add a range name such that it is easy to edit it is using the Name Manager (CTRL + F3):
     

  • In Excel 2007 and later versions, if you click on ‘New…’ (above), the following dialog box appears:

  • Note the highlighted section (Scope).  All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). 
  • Never duplicate a range name within a workbook.  It can cause formulaic errors.  Also, try never to have range names on a worksheet that may be copied.

Deleting Range Names

  • Use the CTRL + F3 shortcut for the Name Manager box to appear:

  • Use the CTRL or SHIFT buttons to make multiple selections before hitting the ‘Delete’ button.  In fact, range names may be even filtered to find names with errors, scoped to the workbook, scoped to the worksheet etc.

Login to download
  • 00:03 >> Range names are a useful feature in Excel, when used sparingly.
  • 00:12 >> The idea behind the range name is that a cell reference can be replaced by
  • 00:17 text by using a bunch of functionalities found in
  • 00:20 the defined names section of the formulas tab of the Excel ribbon.
  • 00:26 Let's look at some ways of using range names in this first of three parts.
  • 00:33 We're going to start with the rudiments of range names.
  • 00:37 You've noticed that I'm in cell A1.
  • 00:39 You can see that column A and row 1 are highlighted, and that
  • 00:44 the cursor is into A1, and also there's a little box just above it this says A1.
  • 00:49 If we bring the mouse into play here, you will see that this is A1.
  • 00:54 Now it's B5, this is known as the name box.
  • 00:59 Now we can rename cells.
  • 01:02 I can have in here, let's say, three member's of the Simpson's family.
  • 01:06 Fred Simpson, Wilma Simpson,
  • 01:11 and Barney Simpson.
  • 01:15 Press on the keyboard extra hard just so you can get the full stereo effect.
  • 01:21 Right, let me show you how we can rename these cells.
  • 01:24 I am going to B3 here, and I'll just type in here Fred.
  • 01:30 Press Enter, that is now Fred.
  • 01:33 If I go over here and type in =fr, do you see Fred comes up.
  • 01:37 If I tap and press Enter, I've now got Fred in there.
  • 01:42 Fred equals Fred.
  • 01:45 I type 17 in there.
  • 01:47 It's now 17.
  • 01:49 If i try and name this cell Fred, as well, it wont do it.
  • 01:57 It goes back to cell B3.
  • 01:58 So the first lesson we learn here
  • 02:01 is that once you've defined a range name on a sheet, that's it for that sheet.
  • 02:06 Let's stain this one while we're doing it a different way.
  • 02:09 I hear from the home town I can go to formulas and
  • 02:12 go the main section to define names.
  • 02:14 Click on this and you'll see that it automatically suggests Wilma,
  • 02:18 cuz that is the contents of that cell.
  • 02:21 It gives me a choice for scope of either workbook or
  • 02:24 specific to the worksheet I'm on, sheet one.
  • 02:27 If there were more worksheets in the workbook they would appear here.
  • 02:31 The thing you should do is always make them workbook specific.
  • 02:34 It can cause problems if you don't do that.
  • 02:38 So can you please make sure that you go workbook specific.
  • 02:41 Excel has an issue if you start having sheet one range name,
  • 02:45 sheet two range name, and it's the same name.
  • 02:48 Notice then it is says sheet one b4, and it's got those dollar signs Which means,
  • 02:53 it's been anchored, it's always B4.
  • 02:54 Click OK, that's now called Wilma, and it joins Fred.
  • 02:59 Now, to do Barney, we're a different way.
  • 03:02 Keyboard shortcut, hold the Ctrl button down and press F3.
  • 03:06 This brings up the name manager and
  • 03:09 I can then click on new to come into Barneysville this way.
  • 03:13 I can instead come out of that and
  • 03:16 click on name manager here in the define name section of
  • 03:20 the formulas tab of the ribbon and that gets me to the same place as well.
  • 03:25 So Barney Fine.
  • 03:28 I now have three.
  • 03:30 They'll all appear here, in alphabetical order.
  • 03:34 Now, what I want to, removal, what if I just go here, and go?
  • 03:40 Doesn't help.
  • 03:41 if I type in B5, just takes me back there, but it's still Barney.
  • 03:47 How do I delete Barney?
  • 03:48 We'll cover up a high row.
  • 03:49 I'm gonna delete.
  • 03:51 That looks good.
  • 03:54 I still got Fred and Wilma left.
  • 03:59 Yes!
  • 03:59 it's worked.
  • 04:01 No, it hasn't.
  • 04:03 Let me show you by going to the name manager.
  • 04:05 Ctrl+f3, click on the name manager box.
  • 04:08 You will see Barney is now #REF!.
  • 04:12 This is what we call a redundant range name.
  • 04:15 This range name no longer refers to any given range and
  • 04:19 it's actually causing a reference error.
  • 04:21 This is one of the top causes of file corruption in Excel and should be avoided.
  • 04:26 It's an easy fix.
  • 04:27 You just need to delete them.
  • 04:29 Now, since Excel 2007 came out,
  • 04:32 Name Manager has made it easy to select more than one name change at a time.
  • 04:35 I can select all three if I want to.
  • 04:37 I just want to select Barney.
  • 04:39 If there was another error in here, I could use the filter drop down box, and
  • 04:44 go to names with errors.
  • 04:45 Again, it tells me how it's Barney.
  • 04:47 Highlight it, click delete, and that is how you really delete a range name.
  • 04:53 Now if I remove the filter, I've only got Fred and Wilma left.
  • 04:58 So be careful when using range names because of the fact
  • 05:03 that they're more difficult to delete then you might first think.
  • 05:06 In the next section, we'll look at some more applications of range names.

Lesson notes are only available for subscribers.

Gift this course