So there you are, editing a named range in like the Excel superstar you are, when you make the mistake of hitting the left arrow key to go back a few characters in your formula. Excel instantly references the cell to the left. Argh!
Well this can be avoided by simply pressing F2 while you’re in the ‘Refers to’ field. This will allow you to safely move back and forth using arrow keys.
Second post, first comment.
Judging by this item I’m going to have to visit regularly. This is going to be SO useful!!
Thanks – and good luck with the new blog!
Great tip.
But here’s food for thought: why the hell don’t Microsoft make the ‘Refers to’ part of the dialog box bigger to start with. And why doesn’t the ‘Refers to’ part of the box resize when you resize the whole ‘Edit Name’ box?
Even worse, when you click on the reddish mystery icon to the right of the ‘refers to’ part, you can only resize the resulting ‘Edit name – Refers to:’ box horizontally! Even though it looks like it’s supposed to resize vertically ( if you select the stetchy bit and drag it down, it tantilisingly flickers between a bigger vertical selection, and the existing vertical dimension, but reverts to existing vertical dimension when you let it go.)
Why the hell is so much stuff basic stuff in Excel 2007 still suffering from the crappy usability flaws of Excel ’97? It would have been incredibly easy to identify and then change these simple but major annoyances with little programming overhead.
So Microsoft – please put new functionality on hold while you do simple no-brainers. For example, make the SQL statement input box in Excel’s MS query functionality bigger than a matchbox, so it’s able to display more than just ‘Select All’. Duh!
Great blog, Mike. Sorry for the rant…
I see you have feelings on this issue.
Great tip. Keep it up. This one hits me a few times, once I hit that arrow key, my way out is to start over.
Yep, I’ve got feelings. But I’m seeing a blog, which is really helping get my anger under control
I just can’t get my head around the fact that MS can do something so right like spot need for putting a Wrap Text button on the ribbon (an improvement they spotted because their 750 billion user session database showed that users were routinely making 17 mouse clicks from start to finish to wrap text pre excel 2007), and yet miss resizing dialog boxes now that we all have screens bigger than the ones we had 10 years ago.
Perhaps they rely too much on that usibility database…that only shows what people CAN do…rather than interviewing and videotaping users to find out what they CANT do.
Oh my god, I just realised I’m channelling Seth Godin again.
Fantastic! You’ve relieved me of great pain
Just started watching some of your videos… great stuff… thank you very much for sharing your invaluable excel tips! Will definitely be coming back to see the DataPig!!
3ks,got it
is there anyway to do this using Excel on Mac?
Geoff: I honestly don’t know since “I’m a PC”. But I can’t imagine it would be hard to try it.
We’ve asked MSFT about a zillion times if they could update the refedit control (which is what we’re talking about here).
It didn’t make the cut for 2007. No idea what 2010 will bring.
3 entries- 2 new things learned- a pretty good average!
I will definitely be adding this to my most visited Excel sites
Hello, can you please post some more information on this topic? I would like to read more.
Damn and blast, why did no one tell me this before??
Another blog I shall have to check out on a daily basis…
Thanks!
Waaaay Cool. This has frustrated me many times!
Awesome bit of knowledge, thanks for sharing!!!