Formulas that contain a named range are more easily understood because it states more specifically what data is being used in the calculation. In the example above, it is easier to understand what it means to sum Sales. Simply referring to range of cells is less intuitive.
Ways to Use Named Ranges:
• use them in function arguments requiring a range of cells
• aid in writing understandable, complex formulas and functions
• quick method for referencing cells in formulas
• set print ranges for creating reports from large worksheets
• establish source data range for pivot tables
• set-up data range for creating charts
Named ranges can be particularly useful when you are repeatedly using a range of cells that are not in adjacent cell locations. You can select all the cells by holding the CTRL key down while selecting the non-adjacent cells. Once you have named the group selection, you can easily substitute the named range in each instance where you need that group of cells.
For example: You have taken a survey and want to chart the results by category headings. Each category has 3 – 7 sub-questions. You can select the category headings once, define a range name to represent the selection, then use the named range repeatedly in your charting series.
Defining a Named Range
The simplest method for creating a named range is to use the Name Box on the Formula Bar.
1. Select the cell, range of cells, or nonadjacent selections that you want to name
2. Click the Name box at the left end of the formula bar
3. Type the name for the cells
4. Press ENTER
The key to your success in using this technique is to make sure you use the ENTER key after you define the name of the range in the name box. If you simply click off into a worksheet cell, the named range will not be saved.
Note: You cannot name a cell while you are changing the contents of the cell.
Rules for Named Ranges
• can contain up to 255 characters in length
• are not case sensitive
• cannot contain spaces
• cannot contain special punctuation characters except the underscore
• cannot resemble a cell address
Once you have defined your named ranges, you can select them from the Name Box drop down found to the left of the formula bar.
Editing a Named Range
You cannot edit the range selection for a named ranged using the Name box. You must use the Define Named Range dialog box. Select Insert | Name to display the Define Named Range dialog box.
 > |
You will see the range reference for the selected Named Range in the Refers to: field. To redefine:
• Click inside the Refers to: field
• Use the collapse button (on the right hand side of the field)
• Make an appropriate range selection on your worksheet
• Restore the Refers to: field by clicking on the collapse button
• Click on OK
Note you can also define a named range and delete ones you no longer need using the Define Name dialog box.
Paste Name
The Paste Name feature allows you to paste a copy of the list of named ranges contained in your workbook.
Press F3 to display the Paste Name dialog box, then click on the Paste List button.
 > |
This feature can be useful when you have a large number of Named Ranges that you are trying to keep track of. When you Paste List, you will get a list of the Named Ranges and the references stored in the Refers to: field.
 |
When you need to reference the same range of cells repeatedly in your formulas or print setup commands, use Named Ranges to speed up your development time. Once you start to use them, you will find many practical uses for them.
