g Text Version
Beauty & Self
Books & Music
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
News & Politics
Religion & Spirituality
Travel & Culture
TV & Movies

Bored? Games!
Take a Quiz
Rate My Photo

Natural Living
Folklore and Mythology
Distance Learning

All times in EST

Low Carb: 8:00 PM

Full Schedule
g Desktop Publishing / Ebooks Site

BellaOnline's Desktop Publishing / Ebooks Editor


Using Named Ranges (Excel)

Guest Author - Chris Curtis

Which formula is more understandable to you?




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.

Add Using+Named+Ranges+%28Excel%29 to Twitter Add Using+Named+Ranges+%28Excel%29 to Facebook Add Using+Named+Ranges+%28Excel%29 to MySpace Add Using+Named+Ranges+%28Excel%29 to Digg Using+Named+Ranges+%28Excel%29 Add Using+Named+Ranges+%28Excel%29 to Yahoo My Web Add Using+Named+Ranges+%28Excel%29 to Google Bookmarks Add Using+Named+Ranges+%28Excel%29 to Stumbleupon Add Using+Named+Ranges+%28Excel%29 to Reddit

5 Unique Techniques for Excel
Converting a List of Names to Multiple Columns in Excel
Excel - Working with Worksheet Tabs
Related Articles
Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map

For FREE email updates, subscribe to the Desktop Publishing / Ebooks Newsletter

Past Issues

Printer Friendly
tell friend
Tell a Friend
Email Editor

Content copyright © 2018 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.


g features
Becoming a Fiction Editor

Making a Background Transparent Using Photoshop

Merging Letters in Word 2007

Archives | Site Map


Past Issues

Note: BellaOnline uses cookies to help provide a consistent user experience. Our advertisers may use cookies to help customize ads. Please contact us with any question about our cookie use.

Summertime Foods
Corn on the Cob
Burgers on the Grill
Apple Pie


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

BellaOnline Editor