Loading

May 312012
 

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

This is part 2 of a 3 part post. Continued From Defined Names 101: What Are Defined Names?

Creating Named Ranges

There are two main ways to create Named Ranges on an individual basis.

The first way: the Quick Method

XL2003 NameBox

Excel 2003

XL2007 NameBox

Excel 2007

  1. Select the Cell or Range you want to name.
  2. Click on the Name box (to the left of the Formula box). This box will display the address of the selected range as you select it and then show the address of the Top Left cell in that range when you stop selecting it. When you click in it, the Top Left cell address will become selected. Over type this address with the name you want to use and press the Enter key. If the name is accepted, then it will show in the box. If not, Excel will warn you that the name is invalid. The name will be rejected if it contains invalid characters (see Syntax described in the previous post). If you type in a cell or range reference, Excel will select that cell or range.
  3. If you enter a name in the Name box, the name will be scoped to the workbook. If you wish to scope the name to the active worksheet, then you must qualify the name with the active worksheet name. e.g. Sheet1!MyName [Note the use of the “!”]

Using the Name box is a quick and easy means of adding names. However, there are a few drawbacks:

  1. If you type a name that matches an existing Named Range, then instead of creating that name, Excel will select it, even if the name refers to a range on another sheet.
  2. If you mis-type the name, you cannot correct it via the Name box once you click the Enter key.
  3. If your Named Ranges are more than 18 (around 14 for Excel 2003) characters long then the ends of the names are truncated making it hard to tell them apart, especially if many have a similar beginning. However, Excel 2007/2010 lets you resize the Name box
  4. The Name box will not show the scope of the Named Range.
  5. If adding a name with a worksheet scope, you can only use the Name box to scope a name to the active worksheet.

The second way (Excel 2003): Using Define Name

Excel 2003 Define Name

Excel 2003 Define Name

  1. Select the Cell or Range you want to name.
  2. Go to Insert>Name>Define… to open the Define Name dialog box showing a list of all the names already defined in your workbook.  The Refers To box at the bottom will show the range selected in step 1 above. Enter the name you wish to define in the blank box at the top and click the Add button. The new Named Range will appear in the list.
  3. If you make a mistake with the selected range, simply click in the Refers To box and then select a new range on the worksheet, or simply edit the reference displayed and click the Add button again. This will update the reference for the currently selected name. This method can be used to edit other names in the list, simply by selecting them and changing the Refers To value.
  4. If you make a mistake with the actual name you have used, then you can edit the name and click on the Add button. This will actually not modify the currently selected name but will add a new one with the modified name. You can then simply select the incorrect name and delete it.
  5. If you enter a name in the Name box, the name will be scoped to the Workbook. If you wish to scope the name to the active worksheet, then you must qualify the name with the active worksheet name. e.g. Sheet1!MyName [Note the use of the “!”, also, you can only create a name scoped to the active worksheet]
  6. When you are done, close the Define Name dialog box.

The benefits of using this method are:

  1. The Define Name dialog box is used for managing Defined Names which means that you can easily correct errors, make amendments and delete names created this way.
  2. You can see the scope of the Named Range.
  3. The dialog box is wider than the Name box so you can see the full name.

The second way (Excel 2007/2010): Using New Name

Excel 2007 Define Name Menu

Excel 2007 Define Name Menu

Excel 2007 New Name

Excel 2007 New Name

  1. Select the Cell or Range you want to name.
  2. Click on the Define Name menu in the Defined Names section of the Formulas tab. The New Name dialog box will appear. Enter the name you wish to use in the Name edit box at the top, choose the Scope, add a comment if you wish, check the Refers To box shows the correct reference (this will be based on the range you selected in Step 1 above) and click on OK button.
  3. If you have made a mistake with the selected range, simply click in the Refers To box and then select a new range on the worksheet, or simply edit the reference displayed and click the OK button.
  4. You can resize the New Name box to show the entire Refers To reference if needs be.
  5. The New Name dialog box will close and the name is added to the list of Defined Names in the workbook.

The third way (Excel 2007/2010): Using the Name Manager

See screenshot in section “Managing Defined Names in Excel 2007/2010” below.

  1. Select the Cell or Range you want to name.
  2. Click on the Name Manager menu in the Defined Name section of the Formulas tab. This will open up the Name Manager dialog box. To add a new name click on the New button and that will open up the New Name dialog box.
  3. These next part is the same as clicking directly on the New Name button described above. The only difference is that after you click on the OK button, you will return to the Name Manager dialog box which you can close when you have finished.

The benefits of using this method are:

  1. The Name Manager dialog box is used for managing Defined Names which means that you can easily correct errors, make amendments and delete names created this way.
  2. You can see the scope of the Named Range.
  3. The dialog box can be resized so you can see all the information relating to the Defined Name.

Personally, I usually use the Quick Method for adding individual names but if I have a few to add, I will go straight to the Name Manager. I rarely call the Define Name directly. (In Excel 2003 I go straight to the Define Name dialog box).

The automatic way: Creating Names from Selection

There is another method for creating Named Ranges whereby Excel will create the names automatically based on Row or Column headings. In Excel 2003, this is done by using the Create Names dialog [Insert>Name>Create menu]. In Excel 2007/2010 it is done using the Create Names from Selection dialog [Create from Selection menu in the Defined Names section of the Formulas tab]

I would NOT recommend using this method in any workbook for the following reasons:

  1. The names are created directly from the Row or Column labels and Excel will automatically substitute any disallowed characters, including spaces, with an Underscore (_). This makes it hard to follow any strict naming convention.
  2. A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels. This removes control over creating the names.
  3. You cannot control the Scope of a name using this method. All names created are scoped to the Workbook. This is not a problem if you do not want worksheet level names.
  4. This method is often recommended when users need to create a lot of names. However, if you have ever used a workbook with a lot of names, you will see how important a good naming convention is.
  5. If you only have a few names, then you may as well create them manually and retain control over the process.
  6. The names that Excel creates do not follow my guidelines for using names when building robust models.

Note: In Excel 2003, there was also an option to use Labels in formulas. This offered a way to use the Row and Column headings directly in cell formulas. However, this option was removed in Excel 2007. And what a relief that was. Labels enabled people to use something similar to Named Ranges without actually creating any names. It was silly and dangerous and caused no end of problems. Its removal was a blessing! In place of Labels, Microsoft beefed up its names management by introducing the Names Manager.

Special Points About Creating Names

  • It is possible to create more than one name for the same cell or range of cells. The Name box will display all the names although the name displayed when the cell is selected is the first name alphabetically.
  • You can define names that refer to non contiguous cells. Hold down the Ctrl key to select these non-contiguous cells before creating the Named Range.

Managing Defined Names in Excel 2003

In Excel 2003, the management of names is quite tedious. Modifying or Deleting names is done via the Define Name dialog. This is also one of the ways we used to create names. Go to Insert>Name>Define… to open the Define Name dialog.

Deleting a Defined Name: To delete a name, simply select it in the list of names and click the Delete button. [Note: you can only delete one name at a time]

Modifying Refers To: To modify what a name refers to, simply select it from the list and either directly edit the reference in the Refers To edit box or select the reference in the Refers To edit box, then select a new range from the a worksheet. When the cursor is in the Refers To edit box, if you click on a cell, its reference will be added to the Refers To reference. If you select a range, the Define Name dialog will be minimized and each range will be added to the Refers To reference. If you simply wish to replace the current reference with another one, make sure that the existing reference in the Refers To edit box is selected before you click on the worksheet. Alternatively, delete the existing reference before selecting the worksheet. Once the reference is correct, click on the Add button. Excel will overwrite the existing name with the modified one.

Modifying the Name of a Defined Name: In Excel 2003, you cannot directly change the name of a Defined Name. If you wish to change the actual Defined Name itself, first select the name from the list, then edit its name and click the Add button. Excel will add a new Defined Name with the same Refers To reference. Once you have added the new Defined Name, you should delete the old one. You must then replace all uses of the old name with the new one in every formula. If you have followed my strict naming convention, then you can safely use Excel’s Find and Replace to do this. Remember, Find and Replace will replace everything on your worksheet or workbook including all text as well as the Defined Names used in formulas.

Managing Defined Names in Excel 2007/2010

In Excel 2007, Microsoft dramatically improved Defined Names management with the introduction of the Name Manager. Modifying or Deleting names became a breeze.

Excel 2007 Name Manager

Excel 2007 Name Manager

To open the Name Manager, click on the Name Manager menu in the Defined Name section of the Formula tab. This dialog lets you Add, Edit and Delete names efficiently. We have already covered creating names in a previous section. Clicking the New… button takes you directly to the New Name dialog.

Deleting Defined Names: To delete names, simply select them from the list of names and click the Delete button. Excel will ask for a confirmation before actually deleting the names. To select a group of contiguous names from the list, click on the first name you wish to delete, then hold down the Shift key and click on the last name you wish to delete. Everything between the two names will be selected. Use the scroll bar if the last name is not visible. To select a group of non-contiguous names from the list, hold down the Ctrl key and add names to the group. Click the Delete button when all are selected.

Modifying: To modify a Defined Name, select it from the list and click on the Edit… button. You can only edit one name at a time. This will open up the Edit Name dialog which allows you to edit the Name, what it refers to and change the comment.

Changing what a Defined Name refers to is similar to Excel 2003. Either directly edit the reference in the Refers To edit box or select the reference in the Refers To edit box, then select a new range from the a worksheet. When the cursor is in the Refers To edit box, if you click on a cell, its reference will be added to the Refers To reference. If you select a range, the Define Name dialog will be minimized and each range will be added to the Refers To reference. If you simply wish to replace the current reference with another one, make sure that the existing reference in the Refers To edit box is selected before you click on the worksheet. Alternatively, delete the existing reference before selecting the worksheet. Once the reference is correct, click on the OK button. Excel will update the Refers To reference and return to the Name Manager.

To change the name of a Defined Name, simply edit the name in the Name edit box and click the OK button. Excel will update the name and the new name will be updated throughout the workbook, wherever it is used. [Note: you cannot change the scope of a Defined Name once it has been created]

The Name Manager can be resized, columns can be re-ordered by clicking and dragging the column titles and columns can be sorted by clicking on the column titles. Double click the Name Manager title bar to maximize to full screen size. You can also filter the selection of Defined Names using the name Filter button. The filter is especially useful for finding Defined Names with errors and for reviewing the scope of names.

You can also change what a Defined Name refers to directly in the Name Manager by selecting the name and editing the reference in the Refers To edit box at the bottom. Click on the cross or tick to reject or accept the change.

Special Points About Deleting Named Ranges

  • If you delete a Named Range which is being used in a formula the formula will return #NAME? indicating that the Defined Name no longer exists.
  • If you delete cells or ranges referenced by Named Ranges, then these Named Ranges will become invalid. The cell references used by the Named Ranges will then contain #REF! but the Defined Names are not automatically deleted. This can be quickly remedied by simply redefining the Refers To of the Named Range so it points to a valid cell or range.

This is part 2 of a 3 part post. Continued in  Defined Names 101: Using Defined Names

May 292012
 

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is the first part of a 3 part post A Defined Name is a way of adding a meaningful descriptor to any of the following: A Cell A Range of Cells (including an entire row or entire column) A Constant (i.e. a number like 8.3) A Formula First Name Last Name Email Address Cell =C1 =TaxRate Range of […]

Continue »

May 252012
 
Excel Intersection 101: Explicit Intersection

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is part 2 of a 2 part post. Continued From Excel Intersection 101: Implicit Intersection As opposed to using Implicit Intersection, you can also use the intersection operator to force an Intersection between rows and columns. This intersection is called Explicit Intersection. The Intersection operator is a “Space”. First we will look as some simple ways to use it. […]

Continue »

May 242012
 
Excel Intersection 101: Implicit Intersection

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 Excel has a little known but very powerful feature that can help users to build much simpler and easier to read formulas and, if used correctly, can also help to make models much easier to modify without breaking  references or referencing the wrong cells. The key to this is Implicit Intersection. First we will look as some simple ways […]

Continue »

May 232012
 

Here it comes, finally, the first post. It’s taken a bit longer than I thought or hoped. Actually, a lot longer! There’s me believing all the online hype about how quick and easy it is to set up a blog site. Let me tell you, it really is not. Or is that just me? I’m pretty tech savvy and have had a number of simple sites for years. I am also a software developer as […]

Continue »

 Posted by at 11:58 pm on May 23, 2012