Excel Named Ranges

An Excel Named Range is created by allocating a chosen name to a specified range of cells. Once defined, the named range can then be used in functions and formulas, instead of the standard cell reference.

The advantages of using an Excel Named Range instead of a standard cell reference are:

  1. If you use a meaningful name for your named range, this is easier to remember than a standard cell address or range;
  2. Using named ranges makes functions easier to understand and therefore you are less likely to introduce errors;
  3. By default, named ranges use absolute references. Therefore, Named ranges within formulas remain constant as the formula is copied to other worksheet locations.

Excel Named Range Menu:
How to Create an Excel Named Range
Working With Excel Named Ranges
How to Edit an Excel Named Range
How to Delete an Excel Named Range
Simple Spreadsheet Used in Named Range Examples

In order to explain how to use Excel Named Ranges, we use the simple example spreadsheet on the rightshown above, which lists the monthly sales figures for four members of a sales team.

How to Create an Excel Named Range

Method 1: Create a Named Range Using the 'Define Name' Command

Imagine you want to create a named range that refers to the sales figures in the example spreadsheet above (i.e. the named range will refer to the range of cells B2-B5).

The named range is created as follows:

  1. Select the range that you want to name (cells B2-B5 in the example spreadsheet).

  2. Define Name Option in in Defined Names Group

    Select the Define Name option from the ribbon at the top of your spreadsheet. This is found within the 'Defined Names' group on the 'Formulas' tab (see rightabove).

  3. You will be presented with the New Name dialog box, as shown below:

    Excel New Name Dialog Box

    Within the New Name dialog box:

    • Note that the name Sales_Value has been inserted into the Name: field of the dialog box. Excel has automatically taken this name from the column header (in cell B1 of the spreadsheet). As Excel names cannot include spaces, Excel has inserted an underscore in place of the space in the header Sales Value.

      If you want (or if Excel does not insert a default name), you can type any name that you want to use into the Name: field. However, you should be aware of the following rules for Excel names:

      • Names must be no more than 255 characters long;
      • Names must start with a letter, an underscore or a backslash character;
      • The rest of the name should be made up of letters, numbers, underscores or periods (no other characters are allowed);
      • Strings that are the same as a cell reference (e.g. "B1") or the single letters, "C", "c", "R" or "r" can not be used as names.
    • The Scope: field in the dialog box refers to the parts of the Excel workbook where the new name will be recognised. You can select this to be the entire workbook or a single sheet.

    • The Refers to: field has been populated with the current selected cell range. If you want, you can overwrite this with a different range.

    Once you are satisfied that the values in the dialog box are correct, click OK. Excel will then create the new name Sales_Value that refers to the range B2-B5.

Method 2: Create a Named Range Using the 'Create from Selection' Command

Excel also provides a 'Create from Selection' command, that allows you quickly create named ranges from a range of cells containing headers or labels.

A named range for the sales values in the example spreadsheet above can therefore also be created as follows:

  1. Select the range that you want to apply the name to including the column header (i.e. cells B1-B5 in the example spreadsheet).

  2. Create From Selection Option in Defined Names Group

    Select the option Create from Selection from the ribbon at the top of your spreadsheet. This is found within the 'Defined Names' group on the 'Formulas' tab (see rightabove).

  3. You will be presented with the Create Names From Selection dialog box, as shown below:

    Excel Create Names From Selection Dialog Box

    Within the Create Names From Selection dialog box you have the option to use the Top row, Left column, Bottom row or Right column of the selected range for the range names.

    In the example spreadsheet, the top row contains the range name, so we select the option Top row and click OK.

    As Excel names cannot include spaces, in this example, Excel will insert an underscore in place of the space in the header Sales Value, and will create the new name Sales_Value to refer to the range B2-B5.

Method 3: Create a Named Range Using the Name Box

Excel Name Box

You can also create a named range using the Name Box, which is located at the top of your spreadsheet, to the left of the formula bar (see rightabove).

In order to create a named range using the Name Box:

  1. Highlight the range of cells that you want to the named range to refer to;
  2. Type the required name into the Name Box and press the Return (or Enter) key on your keyboard.

Note that the name used must start with a letter, an underscore or a backslash character and the remaining characters must be letters, numbers, underscores or periods. No spaces or other characters are accepted in named ranges.

If a range already exists for the name you typed in, this existing range will be selected. Otherwise, a new named range will be created, which refers to the current selection.

Working With Excel Named Ranges

Name Box

Excel Name Box Showing Named Range

Once you have created a named range, as described above, you can check that it has been created by looking in the drop-down list within the Name Box, which is located at the top of your spreadsheet, to the left of the formula bar (see rightabove).

The drop-down list in the Name Box contains a list of all currently defined named ranges. Selecting a named range from the drop-down list causes that range to be selected and the name of the range to be displayed in the Name Box.

Using Named Ranges In Formulas

Named Ranges can be used in Excel Formulas, in place of cell references.

For example, if you wish to sum all the values in the named range Sales_Values, you can use the formula:

=SUM( Sales_Values )

which is equivalent to the formula:

=SUM( B2:B5 )

Clearly, as your formulas get longer and more complex, the use of names can help to clarify them, and therefore assist in preventing errors.

How to Edit an Excel Named Range

Name Manager Option in Defined Names Group

If you wish to edit a named range simply select the Name Manager option from the 'Defined Names' group on the 'Formulas' tab of Excel (see rightabove).

You will then be presented with the Name Manager dialog box, as shown below:

Excel Name Manager Dialog Box

The Name Manager dialog box lists all currently defined named ranges. Select the name of the range that you want to edit and you can then edit this range by either:

  • Changing the range within the Refers to: field at the bottom of the dialog box and then clicking on the Close button;

or

  • Clicking on the Edit... button at the top of the dialog box. This causes the Edit Name dialog box to be displayed, as shown below:

    Edit Name Dialog Box

    The Edit Name dialog box allows you to edit other features of the current named range, including the name and the range of cells referred to.

How to Delete an Excel Named Range

Name Manager Option in Defined Names Group

If you wish to delete an Excel named range simply select the Name Manager option from the 'Defined Names' group on the 'Formulas' tab of Excel (see rightabove).

You will then be presented with the Name Manager dialog box, as shown below:

Excel Name Manager Dialog Box Delete Button

Select the name of the range that you want to delete and click on the Delete button from the top of the dialog box.

If asked, confirm that you want to delete the Named Range and then click on the Close button to close the Name Manager dialog box.


Further information on Excel Names, including Excel Named Ranges is provided on the Microsoft Office website.