Working with Defined Names
 
  Back to List of Topics
   
  Home
First, some terminology. Defined Names are sometimes referred to by several names, the most common are Defined Names, Range Names and Named Ranges. Lotus created the feature of Range Names in which you assign a name to a range of cells. However, Excel added other features, such as naming formulas. We used to use the terms interchangabely however, to be consistant with the users of today, we will use the term Defined Names.

Working with cells and ranges of cell is fairly simple. Let's say you have a print range B2:G47. Two issues may arise:

  • You get tired of always having to highlight this range everytime you want to print it. 
  • You can't alway remember the exact range, and you need to be consistent.
By defining this range with a name, it will now be much easier and more intuitive to work with.
 

To Name a Range:

  • Select the range, say B2:G47
     
  • Click on Insert (in the menu), Name, Define
     
  • Type the name, for example SalesReport and press Enter
     
  • That range is now defined. 
     
  • To select this range, later press F5 (Edit GoTo) and double click on the SalesReport.  The range is now selected.
Using the  Insert (in the menu), Name, Define  commands are the basic steps to name a range. Here's a quick short cut:
 

Note directly above cell A1, there is a drop down list box with a cell reference in the list. This is the name box.

  • Select a range
     
  • Click the Drop Down Arrow



     

  • Type the desired name, say  Test  and press Enter
    Pressing Enter is required!
    The range is now named.
  • Unselect the range.
     
  • Now, to Go To that range, simply click on the same drop down arrow.
     
  • Click on Text and the range is selected.
     
Short list of rules in naming ranges:
  • Names Ranges must begin with a non-number, usually a letter of the alphabet or the Underscore Character
     
  • The names may contain numbers, but must begin with text
     
  • Periods ( . ) are allowed
     
  • Upper and lower case letters are interpreted the same in Excel.
    SalesReport, salesreport, and SALESREPORT are all the same name. By creating a name using caps, such as SaleReport, you may find it a bit easier to work with.
     
  • The name cannot look like a cell reference. 
    For example, you desire to name your payroll summary W2 (IRS form W2). Excel will think this is cell W2.
     
  • Spaces are not allowed.
     
Changing a range associated with a name:
  • Highlight the new range.
     
  • Click on   Insert (in the menu), Name, Define  (no short-cuts here)
     
  • Type the Name
    Do not select the name from the list. If you do, you simply create the old name and range again. You must type the name.
     
  • Press Enter

 

This article is a quick primer on Defined Names. They are simple to create and very powerful. Most articles in this site will instruct you to used Defined Names as it make the task easier and more intuitive.
 
 

     
    Top

     
     
     
     
     
     
     
     
     
     
     
     

     

    solutions@info-stat.com