Loading

Jun 032012
 

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

This is part 3 of a 3 part post. Continued From Defined Names 101: Creating and Managing

So now you know what Named ranges are for, how to create, modify and manage them. Now we need to look at how to use them. This post will cover using them for navigation and how to enter them in formulas. This will then round up Defined Names 101 series before introducing many of the amazing ways you can use them in formulas along with some introductions to best practices and advanced uses.

Navigating with Named Ranges

There are two simple ways to select a Named Range.

Using the Name box

Excel 2003 Navigate With Name Box

Excel 2003 Name Box Navigation

Excel 2007 Navigate With Name Box

Excel 2007 Name Box Navigation

Simply click the down arrow on the Name box and Excel will display a list of all the Named Ranges in alphabetical order. If the list is long, it will add a scroll bar. Select the applicable Named Range from the list and Excel will then select the range for you, even if it is on another worksheet.

You can also just click in the Name box and type in the name and click the Enter key. However, you must type it accurately as Excel will not auto-match as you type. If you type in a name that does not match a Named Range exactly, then Excel will create a new Named Range. For this reason, I do not recommend using this method.

Note. Excel will display all Names scoped to the workbook and all names scoped to the active worksheet.

Using the Go To box

XL2007 Go To Dialogue

Go To Dialogue

The F5 key is a shortcut key for displaying the Go To dialogue box.In Excel 2003, use Edit>Go To (or F5 shortcut) to open the Go To dialogue box. In Excel 2007/2010 choose Go To on the Find & Select menu in the Editing section of the Home tab. The Go To dialogue box displays a list of all the Named Ranges in alphabetical order preceded at the top by the last four cells or ranges of cells you visited. Select the applicable Named Range and click the OK button or simply double click on the applicable Named Range. Excel will then select the range for you, even if it is on another worksheet.

Note. Excel will display all Names scoped to the workbook and all names scoped to the active worksheet.

If you have a large number of names, then it can take time to scroll through and find the one you are looking for. I often work with a large number of names and in these cases, I will copy a name from a formula and paste it into the Go To dialogue box as this can often be quicker than trying to search for the name in a long list. (Use the keyboard commands for Copy and Paste as this dialogue is modal which means you cannot use the Excel menus when it is open.)

If you select a range on a worksheet that matches the range of a Defined Name, then Excel will display the Defined Name instead of the actual reference in the Name box. This can be useful also if you wish to use this name in a formula, as you can simply click in the Name box to select the name and then copy it using Ctrl C. If you try to use the Copy menu, the name will be deselected and the range will be copied instead.

Note. You can use more than one name to refer to the same range. If  there is more than one Named Range referencing to the same range, then Excel will display the one that comes first based on alphabetical order.

Inserting Names into Formulas (Excel 2003)

There are three main ways:

Type It

You can just type the name directly into the formula if you know it.

Using the “Paste Name” dialogue box

Paste Name DialogueUse the Insert>Name>Paste… (or F3 shortcut) menu to open the Paste Name dialogue box. This shows a list of the available Defined Names. Simply double click  name or select it and click the OK button. This will insert the name into the your formula. One of the key benefits of this method is that you can paste in any defined Name and not just a Named Range (i.e. if the Defined Name refers to a constant or formula). If you use the F3 short cut key, it is also extremely quick and convenient to access.

Paste it

If you have the range already selected, then the name will be displayed in the Name box. Just click on it and copy the name (Ctrl C) and paste it into the formula. This method is useful if you have a huge number of names as it can sometimes be hard to remember what name you are looking for. If you know the cell or range you want to reference, then you can just select that cell or range so the name appears in the Name box. Then copy it and paste it to your formula.

Note. When you start to edit a formula, the Name box becomes a dropdown list of functions, so you need to copy the name from the Name box before you start typing in your formula.

Inserting Names into Formulas (Excel 2007/2010)

There are four ways and starting with Excel 2007, Microsoft made it much easier to enter names directly into a formula.

Type It

You can just type the name directly into the formula. Excel will auto-match the name as you type it and show a selectable list of all the names and functions that match what you type. i.e. if you have five Defined Names (_Cat, _Deer, _Dog, _Donkey, _Fox) and you type in “=_”, Excel will display a list of all five names. If you then type “d” (or “D”), Excel will display the three names beginning with “D”. If you then type in “O”, Excel will narrow the list to just Dog and Donkey. Once the list appears, you can use the mouse to select the desired name and it will be inserted into your formula. This will only work if Excel recognises that you are entering a formula so you must start with an equals “=”.

Excel 2007 Typing Names in Formulas Stage 1

Excel 2007 Typing Names Stage 1

Excel 2007 Typing Names in Formulas Stage 2

Excel 2007 Typing Names Stage 2

Excel 2007 Typing Names in Formulas Stage 3

Excel 2007 Typing Names Stage 3

Note. You can also use the keyboard to enter the name once the list shows. Use the Down or Up arrow keys to navigate to the correct name then use the Tab key to accept it. If you use the Enter key, Excel will accept the formula exactly as you have typed it and you will most likely, get a #Name error. Using the Tab key will enter the name into your formula and leave the cursor just to the right of the name ready for you to continue typing.

Note. Excel will display a list of names and functions when you start to type in a formula. The underscore prefix groups all the Defined Names together so they don’t get lost in amongst the many functions that Excel will also display. See more about naming conventions in a later post. Auto-matching must be enabled.
Note. If Excel 2007/2010 does not display a list of functions and names when you start typing in a formula, then you should check that the Formula Auto Complete is checked in the Excel Options>Formulas. Click here for a screenshot.

Using the “Use in Formula” menu

Excel 2007 Use In Formula

Excel 2007 Use In Formula

Choose Use In Formula on the Defined Menu section of the Formulas tab. This shows a list of the available Defined Names. Simply select a name from the list to insert it into your formula. If the list is long, a scroll bar will be displayed on the right hand side. You can also click the “Paste Names…” menu from the bottom of the list to display the Paste Name dialogue box.

Using the “Paste Name” dialogue box

Paste Name Dialogue

Paste Name Dialogue

Choose Paste Names… from the bottom of the Use In Formula menu on the Defined Menu section of the Formulas tab (or F3 shortcut). This shows a list of the available Defined Names. Simply double click  name or select it and click the OK button. This will insert the name into the your formula. One of the key benefits of this method is that you can paste in any defined Name and not just a Named Range (i.e. if the Defined Name refers to a constant or formula). If you use the F3 short cut key, it is also extremely quick and convenient to access.

Paste it

If you have the range already selected, then the name will be displayed in the Name box. Just click on it and copy the name (Ctrl C) and paste it into the formula. This method is useful if you have a huge number of names as it can sometimes be hard to remember what name you are looking for. If you know the cell or range you want to reference, then you can just select that cell or range so the name appears in the Name box. Then copy it and paste it to your formula.

Note. When you start to edit a formula, the Name box becomes a dropdown list of Functions, so you need to copy the name from the Name box before you start typing in your formula.

Applying Names

If you have created some Named Ranges but still use traditional references for those in your workbook, then you can use this powerful feature that Excel provides to replace all the direct cell references with your newly created Named Ranges.

Excel 2007 Apply Names

Excel 2007 Apply Names

Open up the Apply Names dialog. In Excel 2003 it can be found on the Insert>Name>Apply… menu. In Excel 2007/2010 select Apply Names… from the Define Name menu in the Defined Names section of the Formulas tab [Note click on the down arrow on the Define Name menu]

By default, when the dialog opens, the checkbox Ignore Relative/Absolute is checked. When you create a Named Range, by default, the references are Absolute, i.e. they include the “$”  signs. If you leave the checkbox Ignore Relative/Absolute checked, then Excel will change every reference in the workbook formulas that matches the references of the Defined Names selected in the list whether they are Absolute or Relative (or a combination). In most cases this is the desired outcome but if you uncheck this checkbox, then Excel will require the references to match exactly before it will apply the names.

  One Response to “Defined Names 101: Using Named Ranges”

  1. The single apostrophes are not required when the sheet name has no space, but are when a space character is used as part of the Worksheet name, i.e.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)