1. Determine the address of the currently selected cell. As shown in Figure 1, the Name Box shows B2 because the cell B2 is selected, as indicated by the black border around the cell.
Figure 1: The Name Box typically shows the address of the currently selected cell.
2. Identify the first cell in a contiguous block of selected cells. In Figure 2, I’ve selected a larger block of cells than I can see on one screen. The Name Box displays E3, which identifies the first cell in the upper-left-hand corner of the selection.
Figure 2: When multiple contiguous cells are selected, the Name Box shows the address of the first cell in the selection.
3. Display the last cell in a range of non-contiguous worksheet cell selections. In Figure 3, I held down the Ctrl key while I clicked on cell A1, B6, C11, and then A4. The Name Box shows A4 because I clicked on that cell last
Figure 3: When multiple non-contiguous cells are selected, the Name Box shows the last cell you clicked on.
4. Select a block of cells. In Figure 4, I typed A1:G10 in the Name Box and then pressed Enter. This enables me to select a block of cells without using my mouse or manually traversing the worksheet. Include a worksheet name, such as Sheet2!B5:G48, to select a range of cells on another worksheet.
Figure 4: Select a block of cells by entering the address and pressing Enter.
5. Select multiple blocks of cells. As shown in Figure 5, you can type A1:A10,C1:C10 to select two non-contiguous blocks of cells at once.
6. Select a block of cells by way of the last cell’s address. As shown in Figure 6, click on cell A1, and then in the Name Box, type H10 and press Shift-Enter to select cells A1:H10.
7. Select a two or more columns. Type A:E in the Name Box and then press Enter to select columns A through E. This technique only works when you specify two or more columns; otherwise, you may inadvertently assign the desired column letter a range name to the currently selected cell.
8. Select two or more rows. Type 1:5 in the Name Box and then press Enter to select rows 1 through 5. As with columns, you must specify two or more rows. If you type a number in the Name Box and press Enter, Excel will return an error message. Range Names must begin with a letter or underscore.
9. Jump to any location in the current worksheet by typing a cell address. For instance, type R50 in the Name Box and press Enter to move to cell R50 within the current worksheet. To return to cell A1, type A1 in the Name Box and then press Enter. Or press Ctrl-Home.
10. Jump to any location in the current workbook. To do so, type the sheet name, an exclamation point, the cell address, and then press Enter. For instance, if your cursor is currently in cell A1 of Sheet1 within your workbook, type Sheet2!B12 and press Enter within the Name Box to move to cell B2 of Sheet 2.
11. Determine the last worksheet function you used within the Function Wizard.As shown in Figure 7, when I type an equal sign in cell A1, VLOOKUP appears, because that’s the last worksheet function I chose within Excel’s Function Wizard. If you haven’t used the Function Wizard before, you’ll see SUM appear in the Name Box. (See the article referenced in technique 12 to learn more about this functionality.)
Figure 7: Type an equal sign in a cell to view the last function used in Excel’s Function Wizard.
12. Assign names to individual cells within your worksheet. In Figure 8, I assigned the name Interest to cell B1. To do so, I clicked on cell B1, typed the word Interest in the Name Box, and then pressed Enter. Keep in mind that range names in a workbook must begin with a letter, underscore, or backslash. The rest of the range name can be comprised of letters, numbers, and underscores. Going forward, I can simply use the word Interest in a formula instead of the unwieldy Sheet1!$B$1. To edit or delete range names, use the Name Manager on the Formulas tab of Excel 2007 and later, or choose Insert, Name, Define in Excel 2003 and earlier.
Figure 8: You can use the Name Box to assign meaningful names to individual cells.
13. Assign names to a block of cells. You can select a block of contiguous, or even non-contiguous cells (as demonstrated in techniques 3 and 5), and then assign a name to those cells as shown in technique 12.