The Scenario
You need to change the range referenced by your chart/conditional formatting/named range so you merrily open the Edit dialog box, like the one below for a chart, and start using your arrow keys to move the cursor into position, except all of a sudden the range referenced starts changing before your eyes.
Aaargh, curse you cursor!
The Reason
The reason Excel does this is because the focus of your cursor is actually in the worksheet. You can see in the image below that even though the flashing cursor appears at the end of the series name field the active cell is actually B4.
Pressing the arrow key once to the right will move the active cell to C4 and you'll see the reference in the Series Name field automatically update to =Sheet1’$C$4 like this:
I don’t know about you but I find this about as helpful as a solar powered torch [that's a flashlight for those in the USA].
Don’t get me wrong, I understand the logic but most of the time I just want to type right over the reference myself. After all, I’ve opened the ‘edit’ dialog box, now let me edit it.
The Solution
Ok, I’ll explain a bit more. The F2 key shifts the focus of the cursor to the ‘Edit’ dialog box.
After pressing F2 you can see below that the worksheet cell, C4, no longer has the marching ants around it:
This means the focus of my cursor is now in the ‘Edit’ dialog box.
Now you can merrily use your arrow keys to move the cursor to where you want without it wreaking havoc with your reference.
To shift the focus back to cell C4 simply press F2 again.
This also applies to other dialog boxes you find when working with:
- Conditional Formatting
- Name Manager
- PivotTables and more.
[Update] Another clue as to what Excel is doing can be found in the Status Bar at the bottom left of the screen. You can use your arrow keys in the dialog box when it says 'Edit'.
Here are some screen shots of the different modes you might see:
If you can't see the mode, right click on the status bar (bottom of your Excel window) and choose 'Cell Mode'.
Thanks, Jon and pmsocho who reminded me of this. [end update]
Hi Mynda,
the same goes for editing a formula in a cell: pressing F2 switches from cell select mode to edit mode. I thought to remember that it was toggling, but once in edit mode, pressing F2 again, and then clicking in a different cell, or using the arrow keys, you are just selecting the other cell, and finishing the formula you were working on (maybe just an Excel 2007 issue?).
Hi René,
Yes, F2 in the cell works slightly differently depending on whether you have a formula in the cell or not. It’s like that in all versions of Excel…as far as I know.
Mynda
Hi René,
Excel works in misterious ways sometimes:
In some specific cases, Excel will start operating directly in Point Mode, even if you click inside the formula, with the natural intention to manually edit the reference; if you’re not paying attention to the Input Mode shown in Status bar, if you use the arrow keys or you click on a cell, Excel will insert that reference in the middle of your reference text. And this can be very annoying…
These cases, where Excel will start operating directly in Point Mode are: (may be more cases, if you have experienced these unusual behaviour in situations not listed here, please let us know)
– When creating or editing a Defined Name, while working inside Refers To field,
– When you create or edit a Conditional Formatting formula,
– When you create or edit a chart series.
If you are aware that Excel starts working directly in Point Mode in the 3 cases described above, and you intend to manually edit the formula or the chart series, all you have to do is hit F2 once, and Excel will listen to you like a well trained puppy…
Keep in mind that Excel will switch to Point Mode only from Enter Mode, NOT from Edit Mode! There is no command to manually switch to Point Mode!
Assuming that you worked with Excel in Point Mode, and you pressed F2 once to work in Edit Mode, if you want to operate again in Point Mode after editing the formula, with Excel in Edit Mode, you have to press F2 again to switch from Edit Mode to Enter Mode, then you will be able to use the arrow keys or mouse to select ranges for your formula.
Switching between Input Modes:
– From Point Mode, you can switch to Edit Mode by Pressing the F2 key once;
– From Point Mode, you can switch to Enter Mode by Pressing the F2 key twice;
– From Edit Mode, you can switch to Enter Mode by Pressing the F2 key once.
Hope this will clarify some unexpected Excel behaviours.
Cheers,
Catalin
Hi Catalin,
I had no idea there are three modes working in a cell. Thanks for the info, I am going to check it out to get an understanding of the differences between point and enter mode.
kind regards,
René
Thanks for that simple tip which was tormenting me for years.
🙂 You’re welcome, Shabbir.
That is bugging people all the time! It is one of my gold tricks during my Excel classes because almost no one knows about that 🙂
Mynda, update your great article and add the info, that the you can always see the current mode (edit or point/enter) in the beginning of the status bar of an Excel window 🙂
@ Lynda, glad you liked it. Now you’ll be unstoppable 🙂
@ Jon thanks for the reminder about the cell mode. I forgot about that…probably because I never use it! I’ve updated the post.
@ pmsocho sometimes it’s the little tips that can make a big difference. I’ve added the update about the cell mode. Thanks for the reminder.
Cheers,
Mynda.
Hi Mynda,
It might be worth noting that the Cell Mode (Enter/Edit) is displayed in the bottom left corner of the Excel application window in the status bar. When you press F2, the words “Enter” or “Edit” will be displayed there. This lets you know what mode you’re in. I also find it frustrating to do the accidental arrow key press and get unexpected results in the input box.
If the Cell Mode is not displayed in your status bar, right click anywhere on the status bar and select Cell Mode from the drop-down.
Thanks for sharing this great tip!
Thank you thank you thank you!!! That has got to be one of the most useful tips ever. I do a lot of “copy chart & edit source data”, usually just changing the row numbers, and this has bugged me and slowed me down for years.
And it works in the Conditional Formatting dialog box as well. Between those 2, I’ll be using this tip almost every day!
Awesome! I always have this problem with adjusting conditional formatting. Simple but great tip!
Cheers, Douwe 🙂
Ugh, I have this problem all the time! I already knew about the F2 solution, but it’s still easy to forget if you are in “edit” mode or “annoying” mode. Heck, half the time when I intentionally put it into “annoying” mode it STILL doesn’t work like I want it to. “=Sheet1!A1:A4+Sheet1!B1:B4”? Uhhhh if you won’t take it as a valid reference then don’t automatically put it there for me.
I hear you, Bryan. 🙂
And if you’re in “annoying mode” and you don’t realise it the damage is best repaired with ESC. Then you have to start again.
Hi Mynda,
This is simply wonderful! Never think of a solution as simple as this. Thanks for sharing!
Thanks, MF. Glad you liked it.
Mynda, that’s an awesome tip! That has bugged me for sooooo long! Not that I ever tried to find a solution or even try and understand what it was doing so I can’t complain too much! That’s a very simple explanation thank you!
Me too, Dave. Me too!
Glad you found it helpful 🙂