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.
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.
Not sure how practical this is, but, as I’ve learned over the years, you never know what might be useful down the road.