Twenty-Five Ways to Use Excel's Name Box

Share this content
14

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. 
 
Twenty-Five Techniques
 
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:

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 david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

Replies

Please login or register to join the discussion.

"10. Jump to any location in the current workbook."
How about to any location in any OPEN workbook? :)

Ah, got it. In Excel, assuming the two open workbooks are Book1 and Book2, then with Book1 activated you can type [Book2]Sheet1!A1 in the Name Box box and press Enter to navigate to that location. Thanks, David...I couldn't get this to work when I was writing the article but the problem was I wasn't crafting the syntax correctly to include brackets around the file name. I couldn't seem to get range names in another workbook to work. If you have a technique for that, would you mind sharing?

David:

I think you must include the xtensión of the book you are going to, like [Book2.xlsx]Sheet1!A1.
This worked for me.
Sincerely,
K-Li-Ch

K-Li-Ch, thank you you for the clarification! It may depend whether or not you've saved the file yet or not was to whether the extension is required, but it certainly won't ever hurt to include the extension.

Wow! thanks for this very useful information

You're welcome, and thank you for the feedback!

Thanks David

You can also paste (or type) a function/formula directly into the Name Box that returns a cell or range eg OFFSET. When you press Enter Excel will select that cell/range This is handy if you are testing or auditing a formula.

22 - You missed the keyboard shortcut for the current row - it is Ctrl + Spacebar.

There is also a good shortcut to return you to where you were before following a link. Press F5 then press Enter - I use this all the time.

Neale,

Thank you for contributing to my article! I appreciate your additions.

David

F5 and Enter...Pretty amazing. You get back to the cells you were!

#19 is my most-frequent use for the name box. I'm constantly checking to see how many cells I've select.

You may want to offer a follow-up post to this: 25 Practical uses for the name box. Just because one CAN do these things with the name box, why would one want to? We can all think of reasons for some of them, of course, but when would using each of these tricks be the most efficient way of accomplishing a given task?

Thanks for posting!

David,

I agree with your "just because you could doesn't mean you should" sentiment. The average Excel user will find the list overwhelming and bewildering. However, self-professed Excel junkies such as myself thrive on probing the obscure corners of the program. Each new nuance I learn about Excel sharpens my awareness of what's possible, and often leads to other serendipitous discoveries along the lines of "well, if I can do ____, then surely there's a way to do _____." It's through this endless pursuit that I've accumulated the Excel knowledge that I have today.

Thank you for contributing to the discussion!

David

Greetings,

I'm a director of systems and infrastructure at a university and trying to use excel as a budget projection tool. I have a workbook of "quotes"...for example sheet1 is a quote from dell for 22 servers, sheet 2 is a quote from vmware for 100 vsphere licenses, sheet 3 is a quote from cisco for 2 Nexus 7000 switches, sheet 4 is a quote from cisco for 4 c6807 switches, sheet 5 is a quote from cisco for 2 asa5585x firewalls, sheet 6 is a quote from Compellent for a 100TB SSD SAN, etc. I currently have 307 sheets that comprise my 5 year budget outlook. The higher the sheet's number the less firm the projection. The first sheet is and will always be an Executive Summary, the second sheet is and will always be a detailed summary. All other sheets have the following in common.
1. Quote or Estimate #
2. Hardware Cost
3. Software Cost
4. Support Cost
5. Advanced/Professional Services Cost
6. Shipping Cost
7. Discount
The kicker is that they're never in the same cell's across sheets.

What I've been frantically searching for is the following:
1. A way to change Excel's default name scope from global to local
2. A built in expandable variable for active sheet

What I'm wanting to do seems so simple. I want to click on sheet 3 who's name is "vSphere 5 DR Stretch Cluster", click on the cell with the Estimate # (C5 for this sheet), click in the "Name Box" and type either:
1. quote
2. ./!quote or $!quote or ANYTHING OTHER THAN'vSphere 5 DR Stretch Cluster!quote'...or god forbid using the "Formulas > Define Name" wizard

Then click on sheet 4 who's name is "vSphere 5 EntPlus Licensing", click on the cell with the Estimate # (A2 for this sheet), click in the "Name Box" and type either:
1. quote
2. .`pwd`!quote or $PWD!quote or ANYTHING OTHER THAN 'vSphere 5 EntPlus Licensing'!quote...or god forbid using the "Formulas > Define Name" wizard

etc etc and for all 7 commons across all 39585943938494 gazillion sheets. I've got about 30 min under my belt of asking the google machine and i'm ice cold.

Please tell me excel has an super quick, super short expandable built in variable (like . or ./ or $$ or ^) for active sheet.

Or if someone know's of a better tool then excel I could use for this effort, please feel free to share.

Sorry for the frustration, i don't deal well with stubbing toes on flat ground...and i've skinned my knees, palms, chin, nose and forehead on this little gem...

Thanks

David,

I can appreciate your frustration. The Name Box isn't the right tool for the situation you've described. In this case you'd be better served by a custom macro that would enable you to double-click on an item on a summary sheet and automatically be transported to the detail sheet in question. This isn't something I can describe in a comment like this, plus I'd need to see your actual spreadsheet. If you have someone on staff that knows VBA (Visual Basic for Applications) in Excel, it'd be a pretty easy (and for you, life-changing) macro. Or, this is a typical consulting project for me if you don't have another resource available.

One of my favorite sayings is "Either you work Excel, or it works you!" With the right help you can turn the tide from the latter to the former.

David

Hi. I have a telephone list - I have added a vlookup so that the number returns for said name - but I would like the spreadsheet to automatically move down to said name - in case there are 4 different Ronalds with different surnames - the vlookup is by Name and not name+Surname - As you don't always know the surname