Twenty-Five Ways to Use Excel's Name Box

istock_RapidEye_excel
istock_RapidEye_excel
15

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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Share this content

Replies

Please login or register to join the discussion.

avatar
By David Hager
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
bug
Jun 26th 2015 01:11

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?

Thanks (0)
avatar
By KLiCh
Stephen M Musco
Jun 26th 2015 01:12

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

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

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.

Thanks (0)
avatar
David Hager
Dec 29th 2015 18:21

Dear Sir! If type a cell no in name box of excel 2013, for example E25000, then the cell no E25000 gets highlighted but I want that when I type E25000 in name box then this E25000 should appear at the top left corner of excel sheet and page view should change accordingly. how to do it? Regards

Thanks (0)
avatar
By Ann
Jun 26th 2015 01:11

Wow! thanks for this very useful information

Thanks (0)
avatar
By David Ringstrom
ziee
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By nealeblackwood
Jun 26th 2015 01:11

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.

Thanks (0)
avatar
By David Ringstrom
Marjorey
Jun 26th 2015 01:11

Neale,

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

David

Thanks (0)
avatar
By Felipe Vaz
Marjorey
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Tyler
Jun 26th 2015 01:11

#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!

Thanks (0)
avatar
By David Ringstrom
managingpayables.com
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Dionne
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By David Ringstrom
dringstrom
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Rowan Golob
Jun 26th 2015 01:12

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

Thanks (0)