Working With Named Ranges In Excel 

This page describes the use of Named Ranges in Excel. 

This page has been replaced by Defined Names In Excel.

What Are Named Ranges?

Named Ranges are a powerful tool in Excel that allows you to assign a meaningful name to a single cell or a range of cells.  For example, you can assign the name "TaxRate" to cell C1 and then use the name "TaxRate" anytime you would normally use the cell C1, such as =A5*TaxRate.

There are 3 advantages to using Named Ranges:

  • Formulas are more readable and meaningful. A formula like =A5*TaxRate is more meaningful to you when you are working with a complex worksheet.
  • Named Ranges, by default, always use absolute cell references.  Therefore, you don't have to worry about address translation, which occurs with relative cell references, when you Copy/Paste or Fill Down/Right cell ranges.  (For more information about absolute and relative cell references, click here.)
  • Named Ranges make it easier to create well organized and attractive workbooks. You can use a named reference, rather than a cell address, in formulas, and then define that name to a specific cell after you've designed the workbook.  With Named Ranges, you won't have to edit and change the dependent formulas.  Just change the reference of the name.

Valid Range Names

A range name can contain letters, numbers, and underscores, but not spaces or special punctuation characters.   Moreover, it cannot be the same as a normal cell reference.  For example, "AA10" is not a valid range name because "AA10" is the name of a normal cell reference (row 10, column "AA").

While it is perfectly legal as far as Excel is concerned to use a Name with the same name as worksheet (e.g., you can have the odd situation of having the name 'Sheet1' refer to a cell on the worksheet 'Sheet2'), I would strong recommend against this. It will lead only to confusion.

Adding And Deleting Named Ranges

To create a new Named Range from Excel, use the following procedure:
Select the cell or range of cell that you want to assign a name to.  Go to the Insert menu, select the Name menu item, and type your name in the text box.
To delete a named range, go to the insert menu, select the Name menu item, then select the name from this list, and click the Delete button.

See below for VBA procedures for adding and deleting names. 

Naming Formulas

A Named Range does not have to refer to a cell or a range of cells.  It can refer to a formula.  However, all of the arguments to the formula are "static".   They do not depend on the cell from which the name is invoked.  You should always use absolute cell references in a named formula; otherwise the result can be very confusing.

For example, if you frequently use the formula =SUM($A$1:$A$10), you can create a named formula called MySum, referring to the formula =SUM($A$1:$A$10).  Then, you can use the name anywhere you'd normally use the formula:  =MySum/2

Another advantage is that you can overcome Excel's limitation on eight nested functions. See the Nested IFs page for more details.

Dynamic Ranges

It is often useful to create a name that refers to a range of cells, where the range depends on the content of the cells.  For example, you may want a name that refers to the first N non-blank entries in column A.   Excel's Name tool allows you to do this.  For example, creating a name called DynaRange, referring to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

If the first 20 rows of column A contain data (and the rest are blank), DynaRange will refer to the range A1:A20

See the on-line help for the =OFFSET function for a description of the arguments.  Setting the Width argument to 2 will allow us to use this name in a =VLOOKUP function

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

Then, call VLOOKUP with the DynaRange argument for the lookup range:

=VLOOKUP(C1, DynaRange, 2)

As data is added to columns A and B, the range search by VLOOKUP will extend to include the new data.

Download a workbook illustrating dynamic ranges.


Defined Names In Data Validation And Conditional Formatting

When you use the Data Validation tool, you can restrict valid inputs to the values in another range.  However, the Data Validation dialog won't allow you to specify cells on another worksheet.  But using a defined name, you can get around this limitation.  Simply give the range containing the valid values a name, such as ValidRange, and then in the Data Validation dialog, enter =ValidRange as the Source for the validation list.  Data Validation won't object, and everything will work as you expect. 

You can use the same technique with Conditional Formatting.  The Conditional Formatting objects when you try to use a reference to a cell or range on another worksheet.  Just name the source cell or range, and use the defined name in the Conditional Formatting formulas.   CF won't object, and everything will work as you expect.  See the Conditional Formatting page for more details.

VBA Procedure For Working With Names

Adding A Range Name 

You can use the Add method of the Names collection object to add a new defined name.  

ThisWorkbook.Names.Add Name:="NewName", _ 
        RefersTo:="=$A$1:$C$10", Visible:=True

If you set the Visible property of the name to False, the name will not be displayed in the Names dialog box, although the name can still be used normally in VBA procedures and worksheet formulas. 

If you add a name that already exists, Excel will replace the old name with the new name.  An error will not be generated.  

Determining Whether A Name Exists 

The following VBA procedure will return TRUE if the name specified in TheName exists, or will return FALSE otherwise. 

Function NameExists(TheName As String) As Boolean
On Error Resume Next
NameExists = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function

Deleting A Name  

The Delete method of the Names collection can be used to delete a name. 

ThisWorkbook.Names("NewName").Delete

If the name does not exist, a run time error will occur. 

Determining The Name Of A Cell Or Range

You can use VBA procedures to determine the define named that contains a given cell or range. You must first decide whether you want to return a name that refers to some range that intersects with your range, or whether you want to return the name of the range that refers to exactly the same range or cell as your range.   Below are two VBA procedures to do this.  

Function NameOfParentRange(Rng As Range) As String
Dim Nm As Name
For Each Nm In ThisWorkbook.Names
    If Rng.Parent.Name = Nm.RefersToRange.Parent.Name Then
        If Not Application.Intersect(Rng, Nm.RefersToRange) _ 
            Is Nothing Then
            NameOfParentRange = Nm.Name
            Exit Function
        End If
    End If
Next Nm
NameOfParentRange = ""
End Function

This procedure will return the name of the range that intersects with the range specified by the Rng argument.  For example, if the name Range1 refers to A1:A10, and Rng is A5:D5, the function will return "Range1" because the range A5:D5 intersects with A1:A10.   With this function, Rng does not have be exactly match a defined range -- there simply must be a non-empty intersection between the two.  

If you need an exact match, use the function below.  

Function ExactRangeName(Rng As Range) As String
On Error Resume Next
ExactRangeName = Rng.Name.Name
End Function

With this function, if the name Range1 refers to A1:A10, the function will return an empty string unless Rng refers to exactly A1:A10. 

 
        

There is another class of Name in Excel, whose values are available to all open workbooks, and whose values are retained even after the workbook which created the name has been closed.   See Hidden Name Space for more details.