Jump to Any Cell / Worksheet using Name Box [Quick Tip]

Posted on August 16th, 2010 in Learn Excel - 37 comments

Welcome back. Did you have a good weekend?

We ate fish, watched a movie, had hilarious moments watching the kids and didn’t even leave the house for anything other than to buy some fish.

Coming back to Excel, I want to share a simple productivity tip with all of you. I accidentally learned this and now I use it often to speed up when I am working on large excel files.

Jump to Any Cell / Worksheet using Name Box

If you want to quickly jump to a named range or another worksheet, here is a quick trick. Just enter that name or address in the name box (top-left corner, next to formula bar). And bingo, you are on the target cell or sheet.

See this short demo to understand this trick:

Jump to any cell or worksheet using name box in Microsoft Excel

Bonus tip: You can quickly access defined names using the little down arrow symbol.

Now, this would be even more useful, had I knew the keyboard shortcut for accessing namebox. Alas, I end up moving my mouse pointer to the name box instead.

If you know the keyboard shortcut to access the namebox, share it using comments. Also, share with us, how you simplify navigation in excel using various shortcuts and tricks.

Stuff that can make you productive: Quick, bite sized excel tips, excel keyboard shortcuts, using mouse in excel

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

37 Responses to “Jump to Any Cell / Worksheet using Name Box [Quick Tip]”

  1. Hi Chandoo
    f5 opens the name box - the cursor is in the right field, so you can type in your cell reference there and press enter for the same result.

    1. Press F5
    2. Enter the cell ref (eg "AA322")
    3. Press Enter
    4. Bingo!

  2. Yard says:

    In Excel 2010 (and maybe earlier) you can resize that name box by dragging just to the right of the drop-down arrow. Struggled for years to distinguish between similar long range names!

  3. Hui... says:

    Dito for Ctrl G

  4. Dominik Petri says:

    You can also enter (either in the name box or after pressing F5/CTRL+G) the name of a macro in a standard code module and jump directly to the macro 🙂

  5. Dominik Petri says:

    @Yard: To widen the name box in Excel versions earlier then 2007 look here: http://www.cpearson.com/excel/NameBox.aspx

  6. dan l says:

    Yeah why is there no hot key for jumping to the name box?

    I was just looking for one yesterday since I name so many damned ranges.

  7. Jakob says:

    This trick is very helpful when auditing formulas - highlight a reference in a formula and press f5+enter, or copy the reference, press esc, and paste in the name box or the GoTo window.

  8. Hui... says:

    Dan I
    When entering or editing a formula use the F3 button to bring up a list of named ranges

  9. dan l says:

    Hui,

    I mean the keyboard shortcut to the namebox.

    I'm kind of a hotkey junky.

  10. Hui... says:

    Dan I, you mean like Ctrl F3

  11. notbrl says:

    Too many sheets in your workbook? In lieu of using the scroll buttons or clicking thru tabs to find the sheet you need - right click on any of the scroll buttons and a list of all sheets in the workbook is shown.

    If there are more than 17 sheets there will be a "more sheets" link which when clicked will open a window listing all sheets in the workbook.

  12. dan l says:

    sweet, you've saved my mouse thousands of pointless clicks.

  13. Betty Hughes says:

    For a little fun I once made a tutorial (set to Vivaldi's Concerto in G Major) on how to move around Excel. It's at http://www.youtube.com/watch?v=Fmbtd25IdSw. Cheers!

  14. Rob says:

    The down-side to using the name box is that if you type an invalid range in error, it will create a range name instead of taking you to your desired cell or reporting an error (try 'AAA1').

    Actually, I use this method as an easy way to create range names...never thought of using it as a replacement for Ctrl-G/F5 Go To window.

    Incidentally, the Go To windows doesn't create named ranges, it will just tell you it doesn't exist.

    Rob

  15. Rick Rothstein (MVP - Excel) says:

    Everybody seems to be concentrating on going to a single cell... you can also use the Name Box to select ranges (especially useful for large ranges or ranges that are off-screen. For example, let's say you wanted to fill the range H123:M300 with yellow... this would be a pain to select manually... however, just type H123:M300 in the Name Box and that range will be selected automatically (at which point you could fill it if that is what you wanted to do).

  16. Hui... says:

    Following on from Rick's post

    You can use the Goto Box to select Multiple areas or Intersections of areas

    Multiple Areas use a "," to add the areas together eg: A1:B10,C5:F5

    Intersection use the " " (space) to find the intersection of 2 ranges eg: J5:J20 D10:M10 will select J10 only

    Using named ranges makes these techniques even more powerful.

  17. Rick Rothstein (MVP - Excel) says:

    Following on from Hui's post... the same multiple areas and intersections techniques Hui posted for the Goto Box also work in the Name Box.

  18. Prashant says:

    Thats is good , easy but not come in normal mind...
    u r goooooooood .............chandu ji ........and genius.........

  19. Learn Excel says:

    Great Tip. I normally use Alt E G , for go to. But this one is better 🙂

  20. Allen says:

    Is ther a way to take you to a paticular cell reference from one sheet to other in the same workbook

  21. PM says:

    A lot of us need to create the spreadsheet do a "save as" and then if we need to mail it we attach it to the email etc.. 2 easy ways to do that

    1. to do "save as" --> F12 ... that simple

    2. To send as attachement: Alt + F+ D + A ( dont press togather).. everyone can remember FDA right.. and bamn done

    -PM

  22. h82paint says:

    typing [Sheetname!cell reference] in the name box will take you to another sheet in the same workbook.

    IE: Sheet2!A24 will take you from any sheet in that workbook, directly to sheet 2, cell A24

  23. Tired of it1 says:

    Hi, when I am in excel and typing text, if I hit the space bar after I type a "t", the cursor jumps up into another cell and then I overwrite what is in that cell - it is driving me nuts! Anyone???

    • Hui... says:

      @Tired
      Excel shouldn't do what your saying
      I can type "That works" quite ok

      Try tapping the space and Tab keys several times rapidly
      Then try typing again

      Are you sure your hitting the Space bar and not Tab?
      Is it just in Excel this happens?
      Have you tried a new/another keyboard ?

  24. hello!,I like your writing very much! percentage we keep up a correspondence
    more about your article on AOL? I need a specialist on this house to solve
    my problem. May be that is you! Having a look ahead to see you.

  25. fantastic issues altogether, you simply gained a logo new reader.
    What would you suggest in regards to your publish that you made
    a few days ago? Any sure?

  26. happy mothers day quotes says:

    Great post.

  27. Michal says:

    Oh my goodness! Amazing article dude! Many thanks, However I am experiencing problems with your RSS.

    I don't know why I cannot join it. Is there anybody else getting similar RSS issues?
    Anyone who knows the answer can you kindly respond?
    Thanx!!

  28. reduction says:

    I'll right away snatch your rss as I can not
    find your e-mail subscription link or e-newsletter service.

    Do you have any? Please permit me recognise so that I may subscribe.
    Thanks.

  29. Hari Om says:

    Hi chandoo,

    Please tell me the shortcut key to jump into name box.

    I need to copy the cell address many times. Every time I need to click in the name box & copy that cell address. So please tell me its shortcuts.

    Thanks,
    Hari

  30. SCHUHE says:

    Aw, this was an extremely nice post. Taking the time and actual effort to create a
    great article… but what can I say… I hesitate a lot and don't seem
    to get nearly anything done.

  31. red house painters says:

    Your mode off explaining all in this paragraph is truly good, aall be capable of without difficulty be aware of it, Thanks a lot.

  32. Virtualofficeaddress.org says:

    I like the valuable information you supply in your articles.
    I will bookmark your weblog and check again here frequently.
    I'm fairly certain I'll learn plenty of new stuff right here!
    Best of luck for the following!

  33. web site says:

    We're a group of volunteers and opening a new scheme in our
    community. Your site offered us with valuable info to work on. You've done
    an impressive job and our whole community will be grateful to you.

  34. maquina de coser says:

    I blog often and I seriously thank you for your information. The article has really peaked my interest.
    I am going to book mark your site and keep checking for new
    information about once a week. I opted in for your RSS
    feed as well.

  35. best gamers says:

    Oh my goodness! Impressive article dude! Thank you so much, However I am experiencing difficulties with your RSS.
    I don't understand the reason why I cannot subscribe to it.
    Is there anyone else having the same RSS problems?
    Anyone who knows the solution will you kindly respond?
    Thanks!!

  36. English says:

    After I originally commented I appear to have clicked the -Notify me when new comments are added- checkbox and from now on whenever a comment is added
    I receive 4 emails with the same comment. Perhaps there is an easy method you
    can remove me from that service? Appreciate it!

Leave a Reply