Excel tips: 25 Name Box techniques

In his latest contribution from across the Atlantic David H Ringstrom explores some of the lesser known capabilities of that little rectangle at the top left of every Excel spreadsheet.

Most Excel users think of the Name Box as the area that displays the address of the currently selected cell. Some people may also use the Name Box as a navigation aid. But don’t be fooled, that unobtrusive little rectangle can lead you towards a dizzying amount of useful techniques within. Below are just 25 of the possibilities.

To explore more ideas, also see Simon Hurst’s popular articles on Excel named ranges.

Excel Name Box tips

1. Identify the current cell address. The Name Box illustrated below displays B2 because that is the currently selected cell, as indicated by the black border around the cell.

2. Find the first cell in a contiguous block of selected cells. The next image shows a larger block of cells than will fit on one screen. The Name Box, however, indicates that E3 is the first cell in the upper-left-hand corner of the selection.

3. Show the last cell in a range of non-contiguous worksheet cell selections. If you hold down the Ctrl key while clicking on cell A1, B6, C11, and then A4, the Name Box will display A4 as the last cell that was selected, as seen here:

4. Select a block of cells. The next image shows what happens when you type  A1:G10 in the Name Box and press Enter - a shortcut that lets you select a block of cells without using the mouse. Include a worksheet name, such as Sheet2!B5:G48, to select a range of cells on another worksheet.

5. Select multiple blocks of cells. As seen here, you can also type A1:A10,C1:C10 to select two non-contiguous blocks of cells at once.

6. Select blocks using the last cell's address. Here, the illustration shows that if you click first on cell A1, then the Name Box where you type H10 and press Shift-Enter, Excel will select cells A1:H10 for you.

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. Find the last worksheet function you used within the Function Wizard. The illustration below shows that 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.)

12. Review the last 10 worksheet functions you used in the Function Wizard. If you type an equal sign and then click the drop-down arrow next to the Name Box, you will see a More Functions option at the bottom of the list that will help you pick a previously used function within the worksheet. You can also use this feature to create a "chainsaw" list of worksheet functions.

13. Assign names to individual cells within your worksheet. In the next illustration I used the Name Box to identify cell B1 as “Interest”. This was done by clicking cell B1, typing the word “Interest” in the Name Box and pressing Enter. Range names must start with a letter, underscore, or backslash. The rest of the range name can be comprised of letters, numbers, and underscores. Having named the cell Interest, I can use it 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.

14. Assign names to a block of cells. Pick a block of contiguous, or even non-contiguous cells (as explained in tips 3 and 5), and then give them a name as using tip 13.

15. Navigate to an existing range name. Having created a range name, you can get there simply by typing the name into the Name Box and pressing Enter. If your named range contains multiple cells, you can do a number of things when you get there this way. For instance, you could press Delete to erase inputs, or choose File, Print, and choose Print Selection, as illustrated here. Range names aren't required to print a selection – range names simply make it easy to choose the cells in one go.

16. Use the Name Box dropdown list to get to any named range in your workbook. If you forget the name you chose for a range, the drop-down arrow next to the Name Box will remind you, as shown below. As you select a range from the list, your cursor will move to that location within the workbook.

17. Find 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. If you click a chart, as seen below, the chart name is displayed in the Name Box. You can return to that chart from anywhere in the spreadsheet by typing the name of the object in the Name Box and pressing Enter. 

18. Change names assigned to charts and other objects. When the name of an object or chart is appears in the Name Box, as we saw in the last tip, you also have the opportunity to edit it. By typing in a new name and pressing Enter. An indentifier such as “Fruit” might be easier to find than Chart . Note that while you can't use spaces when naming worksheet cells, you can use do so when naming objects such as charts.

19. Determine the number of rows and columns when selecting a block of cells. The next illustration shows what when selecting two or more cells in a worksheet. While you are holding down the left mouse button, the Name Box will display the number of rows and columns you've selected, but then revert to the range name or cell address when you let go.

20. View a macro within your workbook. When you type the name of a macro in the Name Box, you willl be taken straight into the Visual Basic Editor so you can see the underlying source code.

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. 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 you can do so in Excel 2007 and later versions. This feature is useful if you have long names, or in later editions you can collapse the box down completely to make more room to view  particularly long formulas. To do so, grab the handle just to the right of the Name Box and adjust as needed.

24. Find the intersection of two ranges. Our final illustrations shows what happens when you type A1:C10 B5:D12 in the Name Box and then press Enter. Excel will select cells B5:C10 to identify the overlap between (or intersection of) the two ranges. When using this tip, make sure you include a single space between the range co-ordinates to get the overlap to work.

25. Activate Excel's Ribbon (or the File menu in Excel 2003 and earlier). It may not be something you’ll need to do very often, but if you type a slash (/) in the Name Box, you can activate the keyboard shortcuts in Excel's ribbon, or the File menu in Excel 2003 and earlier.

Thanks to Richard Harker for discovering the special uses of the letters R and C within the Name Box. There are bound to be other uses for Excel’s Name Range box, both mysterious and useful. Feel free to add your own tips below.

About the author

"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.