Use the Name Box to Select a Range in Excel

by Gregory on September 23, 2010

Excel Name Box

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.

Related Posts Plugin for WordPress, Blogger...
Geoff Rees June 8, 2012 at 6:31 pm

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

Gregory June 10, 2012 at 12:34 pm

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 cloud July 20, 2012 at 7:52 am

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-#?

Gregory July 21, 2012 at 1:31 pm

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”)).

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: