Named Ranges in Microsoft Excel
Names ranges can be very useful.
Some people argue that point, whether using named ranges in VBA or formulas. The
most common problem regarding names ranges, everyone agrees, is user ignorance
of their existence in the application.
Create a Named Range
Select a range of cells using the Shift key, Shift+Ctrl, or Ctrl keys (yes,
you can select non-contiguous ranges, like A1, A3, and A5). Then hit InsertàNameàDefine
and type a name.
The name you choose cannot have spaces and cannot begin with a number.
Certain other symbols are also not allowed.
Alternatively, you can type or change the name right into the Name box
(indicated by the red arrow), as shown below. You can name cells, graphics,
textboxes...just about anything.
Tip: You are
not warned when you overwrite a named range.
Hyperlink to a Named Range
You can insert a hyperlink to a named range by inserting a hyperlink to a
workbook, and simply adding #NamedRange to the end of the hyperlink. For
instance, C:\Documents and Settings\YourName\Desktop\data.xls#Master, where
Master is the named range you created.
Find the Range to Which a Name Refers
Use the Name box. It's just above cell A1. Hit the dropdown, and choose a
name. You'll be taken to that range.
Another method: Hit InsertàNameàDefine.
Select the name, and look at the bottom of the dialog to see the range to which
it's been assigned.
John creates a workbook that uses
named ranges to create dropdowns.
John is out sick. You decide you're going to figure out how to add a value to
the dropdown list. You hit DataàValidation, and
find that it's using a list called "fruits".
You find the list of fruits on
Sheet2, and add bananas to the bottom of the list. It doesn't appear in the
When you're looking for a named
range, hit the Name drop-down box, to find the named range, and it becomes
highlighted. The Name drop-down box appears just above column A, which is shown
in the graphic above. Also shown is the selected named range, cells A2 through
A6. To rectify the situation, simply select cells A2 through A7, and hit InsertàNameàDefine
and type "fruits" again.
A very cool use for a named range
is that you can use one cell for your formula values. Suppose you want to mark
up a cost to determine your price. Your costs are all in column B. If you copy
the formula shown below, each row's formula will change. The reference to F1
will change to F2, then F3 as you copy it down each row.
Instead, your could
use absolute references, and type the formula in C2 as:
Or, you could choose
cell F1 and InsertàNameàDefine
and type "markup". Then, you can write the formula in C2 as =B2*Markup. When you
copy it down, only the B2 reference will change for each row, and your markup
will always point to the appropriate cell. This is particularly nice when you're
jumping around to different spreadsheets, and also keeps you from having to
remember what cell the markup value was in.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved