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

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: