David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
By David Ringstrom, CPA
It's pretty much impossible to use Excel and not notice the Name Box, which appears just above the upper-left-hand corner of the worksheet frame. Most users know this as the space in Excel where you can determine the address of the currently selected cell. A smaller subset of users relies on the Name Box as a navigation aid. However, that unobtrusive rectangle belies a dizzying array of functionality in Microsoft Excel.
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.
Figure 5: You can select nonadjacent ranges by typing a comma between each range.
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.
Figure 6: Type the address of the last cell in your desired range and then press Shift-Enter.
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. Determine the last 10 worksheet functions you used in the Function Wizard. To do so, type an equal sign and then click the drop-down arrow next to the Name Box. You can choose More Functions at the bottom if you need to locate a particular worksheet function. You can also use this feature to create a "chainsaw" list of worksheet functions.
13. 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.
14. 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 13.
15. Navigate to an existing range name. If you've already created one or more range names, you can type an existing range name into the Name Box and press Enter to navigate to that range. Unlike technique 9 where we entered a specific cell address, typing an existing range name allows you to navigate anywhere within a workbook. If your named range contains multiple cells, once you navigate to the range, you can carry out any number of tasks. For instance, you might press Delete to erase inputs, or choose File, Print, and choose Print Selection, as shown in Figure 9. Range names aren't required to print a selection – range names simply make it easy to choose the cells in one fell swoop.
Figure 9: You can print a range that you've selected.
16. Use a list to navigate to any named range within your workbook. If you forget the name you've assigned to a range name, click the drop-down arrow next to the Name Box, as shown in Figure 10. When you select a name from the list, your cursor will move to that location within the workbook.
Figure 10: Make a choice from the list to navigate to any named range within a workbook.
17. Navigate to charts and other objects in a workbook. When you create a chart in Excel, or use spreadsheet controls like form buttons, option buttons, and so on, each object is assigned a name. When you click on a chart, as shown in Figure 11, the name of the chart appears in the Name Box. You can later return to that chart from anywhere in a spreadsheet by simply typing the name of the object in the Name Box and then pressing Enter.
Figure 11: Navigate to a chart or other object by typing its name in the Name Box and then pressing Enter.
18. Change the name assigned to charts and other objects. As shown in Figure 11, when you click on a chart, the name of the chart appears in the Name Box. You can then click in the Name Box, type a new name, and then press Enter to perhaps change a chart name from Chart 1 to Fruit so you can navigate to it more easily. Although you can't use spaces when naming worksheet cells, you can use spaces when naming objects such as charts.
19. Determine the number of rows and columns when selecting a block of cells. As shown in Figure 12, when you select two or more cells in a worksheet, the Name Box shows the number of rows and columns you've selected. This statistic only appears while you're holding down the Shift key or left mouse button.
Figure 12: The number of rows and columns appears while you're selecting a block of cells.
20. View a macro within your workbook. As shown in Figure 13, when you type the name of a macro in the Name Box, you'll be transported into the Visual Basic Editor so you can view the underlying source code.
Figure 13: You hop to the source code of a macro by way of Excel's Name Box.
21. Select the current column. Type the letter C and press Enter to select the current column. It's easier to press Alt-Spacebar to select the current column, but this trick confirms that you can't assign the letter C as a range name. If you try to be clever and use the Define Name command instead to assign the letter C as a range name, you'll simply encounter an Invalid Range Name prompt.
22. Select the current row. Type the letter R and press Enter to select the current row. Yes, this means you can't assign the letter R as a range name either.
23. Resize the Name Box. The Name Box isn't resizable in Excel 2003 and earlier, but it is in Excel 2007 and later. This means you can make it larger if you have long names, or in Excel 2007 or 2010, you can collapse it down completely to make more room for viewing particularly long formulas. To do so, grab the handle just to the right of the Name Box and adjust as needed. There's also a hard-core method you can use to extend the width of the range name's drop-down list in certain versions of Excel.
24. Determine the intersection of two ranges. As shown in Figure 14, when you type A1:C10 B5:D12 and then press Enter, Excel will select cells B5:C10 to identify the overlap between (or intersection of) the two ranges. Be sure to include a single space between the range coordinates when utilizing this technique.
Figure 14: You can use the Name Box to identify the overlap between two ranges.
25. Activate Excel's Ribbon (or the File menu in Excel 2003 and earlier). I can't think of any valid reason to do this, but if you type a slash (/) in the Name Box, you'll activate the keyboard shortcuts in Excel's ribbon, or the File menu in Excel 2003 and earlier.
Now it's your turn. Did I miss any tricks that can be carried out by way of Excel's Name Box? If so, please share in the comments box below.
Hat tip to Richard Harker for his serendipitous discovery of how the letters R and C have special meaning within the Name Box.
Read more articles by David Ringstrom.
About the author: