Excel Named Ranges

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. 

   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:

   

   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 a named range 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. 

   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:

   

   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

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

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:

which is equivalent to the formula:

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

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

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

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:

  

  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

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 the Excel ribbon (see rightabove).

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

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.