5 Ways to Create an Excel Named Range

Named ranges are cells, ranges, tables, formulas, or constant values that are represented with meaningful names, that are tied to their workbook reference (i.e. A1, B23, A1:B23). Using named ranges make formulas easier to read and maintain. In this guide, we're going to show you 5 different ways to create an Excel named range.

Name Box

When dealing with fewer number of fields, the easiest way to create an Excel named range is using the Name Box next to the formula bar.

1. Select a cell or range
2. Type a proper name into the Name Box
3. Press Enter

Create from Selection

Create from Selection is a very useful feature that allows adding multiple names at once. As a prerequisite, you need to type in the names in adjacent cells of the cell(s) or range(s) you want to name. This feature works best with cells in a list or table layout containing titles (to be used as names).

1. Select a range that also contains the names
2. Click Formulas > Create from Selection
3. Select the row/header option(s) based on where the names are in your table or list
4. Click OK

Please see How to name multiple cells at once using the Create from Selection feature article for more details on this feature.

Define Name

Define Name is an icon in the Ribbon that can be used to add a single named range. Unlike the Name Box method, the Define Name approach allows selecting a specific worksheet as the scope, and adding a comment.

1. Select a cell or range
2. Click Define Name under the Formulas tab
3. Enter a name
4. (Optional) Set a scope and add a comment.
5. Click OK

Name Manager

Name Manager works very much like the Define Name method. Here, the New button creates new names, and you can manage existing names by selecting them and clicking Edit, or remove them by pressing Delete.

1. Select a cell or range
2. Click Name Manager under the Formulas tab
3. Click New
4. Enter a name
5. (Optional) Set a scope and add a comment.
6. Click OK

VBA (Macro)

You can also use a macro to create named ranges. Using a macro to generate names can be especially useful if you need to add several names in bulk or you want to add names automatically.

Below is simple code block to add a named range. You can build on it, like using an array of names and looping through it to create named ranges. Follow to steps below before going into the code:

1. Press the Alt + F11 combination to open the Visual Basic for Applications (VBA) window.
2. Follow Insert > Module to add an editor module where you will create your code
3. Activate the new window (usually named Module1)
4. Create your code (or copy & paste from below)
5. (Optional) Update the myname and myrange variables

[vb]
Sub AddName()
    Dim myname As String, myrange As String
    myname = "NewName"
    myrange = "Sheet1!B2:D5"
    ActiveWorkbook.Names.Add _
        Name:=myname, _
        RefersTo:=Range(myrange)
End Sub
[/vb]