Defined Names

This page describes how to use Defined Names in your workbooks.

Introduction To Defined Names

A Defined Name is a text descriptor that you can use to describe the meaning of or content of a cell, a range of cells, a constant, or a formula. Once the Defined Name is established, you can use that Name wherever you would ordinarily use the actual cell address or other content. This makes the formulas much easier to understand and maintain. For example, the formula

=G12*K15

isn't very meaningful unless you happen to know the meaning of cells G12 and K15. However, if you assign Defined Names to G12 and K15, you can write your formula similar to the following:

=Income*TaxRate

Clearly, using the Defined Names makes the formula much easier to understand and maintain.

Creating A Defined Name

There are two ways of creating a Defined Name (not including doing so with VBA code). The first method is to select the cell or range of cells to which you want to assign a Name. Then, click in the Name Box and type the Name. The Name Box is the white rectangle to the left of the "A" column heading and above the "1" row heading. If you type in the Name Box a Name that is already defined, Excel will display the range referenced by that Name. It will not overwrite and change the existing Defined Name. The second way to create a Name is to select the cell or range of cells to which you want to assign a Name, go to the Insert menu, choose Name, then Define. This will display the Defined Names dialog box. There, type in the Name you want to use. In that dialog, you can also change the cell(s) to which an existing Name refers.

A Defined Name must begin with a letter or an underscore ( _ ) and consist of only letters, numbers, or underscores. Spaces are not permitted in a Defined Name. Moreover, a Defined Name may not be the same as a valid cell reference. For example, the name AB11 is invalid because AB11 is a valid cell reference. Names are not case sensitive.

CAUTION: If you are working on a workbook that will be used in both Excel 2003 and Excel 2007, be aware that many Names that are valid in Excel 2003 are invalid in Excel 2007. This is due to the increased number of columns in Excel 2007. For example, in Excel 2003, the Name ABC1 is valid because it is not a cell reference. However, in Excel 2007, ABC1 is, in fact, a valid cell reference, so the Name is invalid.

The user interface for working with Defined Names in Excel 2003 and earlier is relatively primitive. Jan Karel Pieterse has created a nice add-in, available for free at www.jkp-ads.com/officemarketplacenm-en.asp, that greatly improves on the basic Defined Name manager. The Defined Name Manager in Excel 2007 and later has been greatly improved over previous versions.

Name Contents

A Defined Name is not limited to referencing a cell or range of cells. A Name can refer to a constant, either a numerical value or a text value. It can also refer to a formula. For example, you could have a Defined Name of MaxRows with a value of 25, and then use that name anywhere you would otherwise use that number. Using a Name has the advantage that if you use it in multiple locations and later need to change the value, you need change it only in the definition and the new value will be used in all other locations.

When you use a Define Name in a formula or cell, you don't include parentheses as you would with a function. For example, if you have a Name defined as MyName with a value of Chip Pearson, you would enter in the cells =MyName, not =MyName(). Using the parentheses would cause a #REF error.

A Defined Name may also contain a formula. For example, you can define a Name named TheSum and assign the formula =SUM($A$1:$A$100) to the Name. Then, you can use =TheSum anywhere you want to get the sum of A1:A100. As noted before, you do not use parentheses in the reference to the name. =TheSum() will cause a #VALUE error. You can use any formula you want in a Name, but you cannot pass parameters to the formula defined by the Name.

Defined Name Formulas And Array Formulas

If you use a formula in a Defined Name, that formula is evaluated as if it were an array formula. There is no way to force a formula in a Defined Name to be evaluated as a non-array formula.

Global-Scope And Sheet-Scope Names

Typically, a Defined Name has global scope. This means that the Name may be defined on any one worksheet and then referenced on any worksheet in the workbook. For example, if you have a cell, say A1 on Sheet1, that contains the last printed date, and you name that cell LastPrintDate, you can use the Name LastPrintDate on any worksheet and it will always refer back to A1 on Sheet1.

It is possible, though, to have Names that are scoped to only a single worksheet, and any or all of the worksheets may have a sheet scoped names with the same name. When that Name is used on a worksheet, the value of the Name on the worksheet that is using it is evaluated. If you have several sheet scoped names, on different worksheets, those names need not refer to the same cell reference. That is, the name SheetLastPrinted on Sheet1 may refer to A1 on Sheet1, and the name SheetLastPrinted on Sheet2 can refer to K10 on Sheet2. When the name SheetLastPrinted is used on Sheet1, the value is taken from A1 on Sheet1. If the Name is used on Sheet2, the value is taken from K10 on Sheet2.

To create sheet scoped Names, select the cell to be named, open the Defined Name Dialog (Insert menu, Name, then Define), and enter the name as Sheet1!TheName (where Sheet1 is the name of the sheet on which the Name should be scoped. The presences of the sheet name causes Excel to make the name sheet scoped. Repeat this process on all worksheets that need to use the sheet scoped name. When defining the Names, enclose the sheet name within apostrophes if the sheet name contains spaces or other non-numeric characters. E.g., 'Sheet One'!TheName.

Defined Names And Conditional Formatting And Data Validation

One of the very useful features of Defined Names is to circumvent some restrictions of Conditional Formatting and Data Validation. In Condition Formatting, if you use the Formula Is method, all cells in the formula must be on the same worksheet as the cell to which Conditional Formatting is applied. For example, if you are applying Conditional Formatting to cell A1 on Sheet1, you will not be able to use a formula like =Sheet2!A10>100, because the formula refers to a cell on another sheet. You can get around this limitaiton by assigning a Name to the cell on the other sheet -- e.g., assign the name TestVal to Sheet2!A10 -- and then use the defined name in the Conditional Formatting dialog: =TestVal>10.

This same method can be used for the values list in a List type Data Validation constraint. If your list of valid values is in the range Sheet2!A1:A10, you cannot reference that range as the source of the validation list if the cell being validated is on a different worksheet. However, if you assign a Defined Name to that range, e.g., ValidList, you can use =ValidList as the values list for Data Validation.

Widening The Defined Name Box

In Excel 2003 and earlier, the defined name box cannot be resized, and it truncates the display of names at about 16 characters. So, if you have two long names like SomeLongDefinedNameOne and SomeLongDefinedNameTwo, you cannot tell which is which in the Name Box dropdown. However, using a bit of code, you can expand the width of the drop down list. This code will not widen the name box itself, as it appears on the formula bar, but widens the drop down list. The code to do this is shown below.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String,  _
    ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, _
    ByVal wParam As Long, lParam As Any) As Long

Sub WidenNameBoxDrop2()
    Dim Res As Long
    Const CB_SETDROPPEDWIDTH = 352
    Const cWidth = 400 '< Change To Desire Width in Pixels
    Res = SendMessage( _
        FindWindowEx( _
            FindWindowEx( _
                FindWindow("XLMAIN", Application.Caption) _
                    , 0, "EXCEL;", vbNullString) _
              , 0, "combobox", vbNullString), _
            CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub

Put all of the code shown above in some code module, and then in your Auto_Open procedure or in the Workbook_Open procedure call the WidenNameBoxDrop2 procedure. For example,

Sub Auto_Open()
    WidenNameBoxDrop2
End Sub

In Excel 2007 and later, the Name Box is sizable, so the code given above in not necessary.

Applying Names To Existing Formulas

You can apply Names to existing formulas. If your formula uses regular cell references and you later assign Names to those cells, you can automatically update the formula to use the Defined Names. For example, suppose you have the formula =A5+D5. If you later assign the Name CellOne to A5 and CellTwo to D5, you can select the cell with the formula, go to the Insert menu, choose Name, and then Apply. Choose the appropriate names in the list, or simply select them all, and click OK. Excel will alter the formula to =CellOne+CellTwo.

Working With Names In Visual Basic

You can use VBA code to work with defined names. To add a name, use

ThisWorkbook.Names.Add Name:="SomeName", _
    RefersTo:=Worksheets("Sheet2").Range("A1:A10")

This will assign the Name SomeName to the range A1:A10 on Sheet2. If you add a Name that already exists, the Name is automatically deleted and recreated with the new reference. It is not necessary to delete the Name before recreating it.

By default, names created manually or with VBA code are visible -- they will appear in the Name Box drop down and in the Names dialog. You can, however, make the name hidden so that it isn't visible to the user. A hidden Name can be used in any manner just like a normal, visible Name. To make a name hidden, you set the Visible parameter to False. For example,

ThisWorkbook.Names.Add Name:="SomeName2", _
    RefersTo:=Worksheets("Sheet2").Range("B1:B10"), _
    Visible:=False

The name SomeName2 can be used exactly as a visible name, but will not appear to the user. The only way to hide a Defined Name is via VBA code. There is nothing in the Excel user interface that allows you to hide a Name.

To delete a name using VBA code, use code like the following:

ThisWorkbook.Names("TheName").Delete

Using Defined Names in VBA code is different than using them in worksheet cells. You must get the value of the Name using the RefersTo or RefersToRange properties. If a Name doesn't refer to a cell or range of cells (e.g., it refers to a constant), an attempt to use RefersToRange will fail. To get the value of a Name that contains a constant, use the RefersTo property.

V = ThisWorkbook.Names("TheName").RefersTo

If the name refers to a range of 2 or more cells, you can assign it to a Range type variable. For example,

Dim R As Range
Set R = ThisWorkbook.Names("BigName").RefersToRange
Debug.Print R.Address

If the name refers to a constant, you'll need to strip out some characters to get the actual value. For example, if the name MyName refers to the text constant Chip Pearson, the RefersTo property will return the text ="Chip Pearson". You need to strip out the leading equals sign and the enclosing quotes. You can do this with code like the following:

Dim S As String
S = ThisWorkbook.Names("MyName").RefersTo
S = Mid(S, 3, Len(S) - 3)
Debug.Print S

If the name refers to a numeric constant, there will be a leading equal sign but no quotes. Thus, you'll need to strip off the leading equals sign. The code below illustrates this.

Dim S As String
S = ThisWorkbook.Names("MaxPages").RefersTo
S = Mid(S, 2)
Debug.Print S

We can put all this together into a function that will return what the given name refers to, be it a range, text constant, or numeric constant.

Function GetNameRefersTo(TheName As String) As String
    Dim S As String
    Dim HasRef As Boolean
    Dim R As Range
    Dim NM As Name
    Set NM = ThisWorkbook.Names(TheName)
    On Error Resume Next
    Set R = NM.RefersToRange
    If Err.Number = 0 Then
        HasRef = True
    Else
        HasRef = False
    End If
    If HasRef = True Then
        S = R.Text
    Else
        S = NM.RefersTo
        If StrComp(Mid(S, 2, 1), Chr(34), vbBinaryCompare) = 0 Then
            ' text constant
            S = Mid(S, 3, Len(S) - 3)
        Else
            ' numeric contant
            S = Mid(S, 2)
        End If
    End If
    GetNameRefersTo = S
End Function

This page last updated: 6-June-2009.