g
Printer Friendly Version

editor  
BellaOnline's Desktop Publishing / Ebooks Editor
 

Using Named Ranges (Excel)

Which formula is more understandable to you?

=Sum(Sales)

or

=Sum(B4:B15)


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.

Desktop Publishing / Ebooks Site @ BellaOnline
View This Article in Regular Layout

Content copyright © 2013 by Chris Curtis. All rights reserved.
This content was written by Chris Curtis. If you wish to use this content in any manner, you need written permission. Contact Laura Nunn for details.



| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2013 Minerva WebWorks LLC. All rights reserved.


BellaOnline Editor