The Name Box and Its Many Uses in Excel

What is the Name Box and What Would I use It for in Excel?

Selecting Cells and Naming Ranges with Excel's Name Box

Ted French

The Name Box is located next to the formula bar above the worksheet area as shown in the image to the left.

The size of the Name Box can be adjusted by clicking on the ellipses (the three vertical dots) located between the Name Box and the formula bar as shown in the image.

Although its regular job is to display the cell reference of the active cell - click on cell D15 in the worksheet and that cell reference is displayed in the Name Box - it can be used for a great many other things such as:

  • naming and identifying ranges of selected cells or other objects;
  • selecting one or more ranges of cells in a worksheet;
  • navigating to different cells in a worksheet or workbook.

Naming and Identifying Cell Ranges

Defining a name for a range of cells can make it easy to use and identify those ranges in formulas and charts and it can make it easy to select that range with the Name Box.

To define a name for a range using the Name Box:

  1. Click on a cell in a worksheet - such as B2;
  2. Type a name - such as TaxRate;
  3. Press the Enter key on the keyboard.

The cell B2 now has the name TaxRate. Whenever the cell B2 is selected in the worksheet, the name TaxRate is displayed in the Name Box.

Select a range of cells rather than a single one, and the entire name will be given the name typed into the Name Box.

For names with a range of more than one cell, the entire range must be selected before the name appears in the Name Box.

3R x 2C

As a range of multiple cells is selected in a worksheet, using either the mouse or the Shift + arrow keys on the keyboard, the Name Box displays the number of columns and rows in the current selection - such as 3R x 2C - for three rows by two columns.

Once the mouse button or Shift key is release, the Name Box again displays the reference for the active cell - which will be the first cell selected in the range.

Naming Charts and Pictures

Whenever a chart or other objects - such as buttons or images - are added to a worksheet, they're automatically given a name by the program. The first chart added is named Chart 1 by default, and the first image: Picture 1.

If a worksheet contains a number of such objects, names are often defined for them to make it easy to navigate to them - also using the Name Box.

Renaming these objects can be done with the Name Box using the same steps used to define a name for a range of cells:

  1. Click on the chart or image;
  2. Type the name in the Name Box;
  3. Press the Enter key on the keyboard to complete the process.

Selecting Ranges with Names

The Name Box can also be used to select or highlight ranges of cells - using either defined names or by typing in the range of references.

Type the name of a defined range into the Name Box and Excel will select that range in the worksheet for you.

The Name Box also has an associated drop down list containing all names that have been defined for the current worksheet. Select a name from this list and Excel will again select the correct range

This feature of the Name Box makes it very easy to select the correct range before carrying out sorting operations or before using certain functions such as VLOOKUP, which require the use of a selected data range.

Selecting Ranges with References

Selecting individual cells or a range using the Name Box is often done as the first step in defining a name for the range.

An individual cell can be selected by typing its cell reference into the Name Box and pressing the Enter key on the keyboard.

A contiguous range (no breaks in the range) of cells can be highlighted using the Name Box by:

  1. Clicking on the first cell in the range with the mouse to make it the active cell - such as B3;
  2. Typing the reference for the last cell in the range in the Name Box - such as E6;
  3. Pressing the Shift + Enter keys on the keyboard

The result will be that all cells in the range B3:E6 are highlighted.

Multiple Ranges

Multiple ranges can be selected in a worksheet by typing them into the Name Box:

  • typing D1:D15, F1: F15 into the Name Box will highlight the first fifteen cells in columns D and F;
  • typing A4:F4, A8:F8 will highlight the first six cells in rows four and eight;
  • typing D1: D15, A4:F4 will highlight the first 15 cells in column D and the first six cells in row four.

Intersecting Ranges

A variation on selecting multiple ranges is to only select the portion of the two ranges that intersect. This is done by separating the identified ranges in the Name Box with a space instead of a comma. For example,

  • typing D1: D15 A4:F12 into the Name Box will highlight the range of cells D4:D12 - the cells common to both ranges.

Note: If names had been defined for the ranges above, these could have been used instead of the cell references.

For example, if the range D1:D15 was named test and the range F1:F15 named test2, typing:

  • test, test2 in the Name Box would highlight the ranges D1:D15 and F1:F15

Entire Columns or Rows

Entire columns or rows can also be selected using the Name Box, so long as they are adjacent to one another:

  • typing B:D into the Name Box highlights every cell in columns B, C, and D,
  • typing 2:4 selects every cell in rows 2, 3, and 4.

Navigating the Worksheet

A variation on selecting cells by typing their reference or defined name in the Name Box is to use the same steps to navigate to the cell or range in the worksheet.

For example:

  1. Type the reference Z345 in the Name Box;
  2. Press the Enter key on the keyboard;

and the active cell highlight jumps to cell Z345.

This approach is often done in large worksheets as it saves time scrolling down or across tens or even hundreds of rows or columns.

However, since there is no default keyboard shortcut for placing the insertion point (the vertical blinking line) inside the Name Box, a faster method, which achieves the same results is to press:

F5 or Ctrl + G on the keyboard to bring up the GoTo dialog box.

Typing the cell reference or defined name in this box and pressing the Enter key on the keyboard will take you to the desired location.