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 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.
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]