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:
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
52 Responses to “Jump to Any Cell / Worksheet using Name Box [Quick Tip]”
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!
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!
Dito for Ctrl G
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 🙂
@Yard: To widen the name box in Excel versions earlier then 2007 look here: http://www.cpearson.com/excel/NameBox.aspx
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.
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.
Dan I
When entering or editing a formula use the F3 button to bring up a list of named ranges
Hui,
I mean the keyboard shortcut to the namebox.
I'm kind of a hotkey junky.
Dan I, you mean like Ctrl F3
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.
sweet, you've saved my mouse thousands of pointless clicks.
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!
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
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).
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.
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.
Thats is good , easy but not come in normal mind...
u r goooooooood .............chandu ji ........and genius.........
Great Tip. I normally use Alt E G , for go to. But this one is better 🙂
Is ther a way to take you to a paticular cell reference from one sheet to other in the same workbook
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
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
I just found about a new shortcut to jump into the value which is used in formula, for example in ur case instead of typing sheet2!A24, just click Ctrl+{ in formula cell and youll be directed to Cell A24 directly.
Hope it helps someone.
Thanks
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???
@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 ?
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.
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?
Great post.
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!!
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.
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
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.
Your mode off explaining all in this paragraph is truly good, aall be capable of without difficulty be aware of it, Thanks a lot.
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!
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.
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.
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!!
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!
First of all I would like to say awesome blog! I had
a quick question which I'd like to ask if you do not mind.
I was interested to know how you center yourself and clear your head
prior to writing. I have had difficulty clearing my mind in getting my thoughts out.
I truly do enjoy writing but it just seems
like the first 10 to 15 minutes are generally wasted simply just trying to figure out how to begin. Any suggestions or tips?
Thank you!
Hi there, yes this piece of writing is actually nice and I have
learned lot of things from it on the topic of blogging.
thanks.
Admiring the commitment you put into your website and detailed information you offer.
It's nice to come across a blog every once in a while that isn't the same outdated rehashed material.
Fantastic read! I've bookmarked your site and I'm adding your RSS feeds to my Google account.
It's hard to find knowledgeable people on this subject,
but you sound like you know what you're talking about!
Thanks
Yes! Finally something about people love.
What's up, I would like to subscribe for this weblog to obtain newest updates, therefore where
can i do it please help.
Very quickly this web page will be famous amid all blogging users, due to it's pleasant posts
I read this article fully concerning the difference of latest and preceding technologies,
it's awesome article.
Before getting into any fat loss or exercise program, you have to first see the ways your body functions.
When baby is 7 months old the following cereal meals, prepared in the home, might be fed.
In women the fat accumulates around hips definitely a pear
shape.
What's up, all the time i used tto check weblog posts here in the early
hours in the morning, for the reason that i love to gain knowledge of more and more.
https://parimatchafrica.co.tz/ parimatch rates
Great tip!
But how do I get back to the place I was originally working from when I found the need to use the named cell, please?
You can press CTRL+G and enter. The go to box will default to previously active cell.
Do you have any video of that? I'd like to find out some additional information.
What's up friends, pleasant paragraph and pleasant urging commented here, I am genuinely enjoying by these.