Use the Name Box to Select a Range in Excel

2010-09-23

The Name box is a quick and easy way to move around and select ranges in a large spreadsheet. While writing this post I also discovered you can select multiple, non-contiguous ranges with it.

The Name box is located next to the Formula bar, however in Excel 2008 for Mac, Formula Bar has to be selected on the View menu before the Name box is visible.

The Name box normally shows the active cell address (like in the picture above) OR the Name of the selected cell, range, or object.

Select a Cell with the Name box

To select a particular cell with the Name box, just type the cell address and hit enter. For example, if I want to go to cell C5000, I just type C5000 into the Name box and press enter. Easy peasy.

The problem is, with large spreadsheets, you don't usually know the cell address right off the top of your head. Naming a range — which I'll cover in my next post — makes it much easier to select or go to a particular cell.

Select a Range with the Name box

Type in the first and last cell address of the range you want to select, with a colon between them, then hit enter. The first cell is the top left of the range and the last cell is the bottom right.

Below I've selected range A2:A16 by using the Name box.

Select Range with Name Box

This technique is useful from a practical standpoint, if your data range is large and your data isn't continuous.

If your data is continuous, a better way to select ranges is shown in Navigating Regions in Excel.

Select Multiple, Non-Contiguous Ranges with the Name box

You can select multiple, non-contiguous ranges with the Name box. Enter each range or cell, separated by a comma, then press enter. In the picture below I select ranges A2:A16, C2:C19, and E2:E3.

Multiple Range Selection Name Box

Not sure how practical this is, but, as I've learned over the years, you never know what might be useful down the road.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

Geoff Rees9 czerwca 2012 01:31
In the drop down box is there a simple way of leaving (saving) a couple of cell references that I frequently jump between in very large spreadsheets, eg A2200 and Z3900
Gregoryexcelsemipro.com10 czerwca 2012 19:34
Yes. Select cell A2200 and type a name in the name box. Select cell Z3900 and type a different name in the name box. Now you can click the drop-down beside the name box, select either of the names you've chosen and jump directly to that cell. Or you can hit the F5 button and select a name from the list and click OK to jump to that cell.
Rick cloud20 lipca 2012 14:52
Can you use the 'Name Box' names programmatically? IE.. if I write a VB program that is going to manipulate the worksheet, can I use the 'names' instead of the cell-#?
Gregoryexcelsemipro.com21 lipca 2012 20:31
Absolutely! You can name individual cell or a range of cells and refer to in in VBA. An example would be naming cell B5 as TestCell. Then changing the value in VBA by using Range("TestCell").value = 24 or if you had a range of cells in column with the name of TestColumn, then you could get the sum of the range by using Application.WorksheetFunction.Sum(Range("TestColumn")).