The simple trick to stopping this is you get in the habit of pressing F2 as the second key stroke after clicking ‘Refers to’ or ‘Source’ fields of these two dialogs and they are tamed and ready to accept your input. The first time I heard this I groaned out loud from the pain and cost of years of not knowing this.
The other aspect to this if you adopt the habit of taking notice of the left hand side of Excel’s status bar (the bottom of your Excel screen) you can easily see when to avoid the muddle that is coming… Plus you will also see other problems straight away like Calculation being left on manual or circular references appearing.
- Before you call up one of these dialogs, the status indicator says ‘Ready’ like it does 99.9% of the time.
- When the dialog is opened it stills says ‘Ready’
- The moment you click your first keystroke into the ‘Refers to’ or ‘Source’ fields, the indicator changes to it’s cruel and sly ‘Point’ mode. So it’s now expecting you to go out to the sheet and point at cells or ranges to define the ranges in the dialog.
- But now that you know what is happening, your second keystroke can be F2. This puts it into edit mode and ready to accept what ever you can type into this cramped space!
The Excel Development team well and truly know about this one and will change this default eventually I would expect. But in the meantime watch and observe your status bar when using dialog boxes and you can skirt around the Excel dialog box muddle.
1.You open the dialog and there is no sign of the trouble to come – note the Status is ‘Ready’
2.You click in the Source field – Status changes to ‘Point’ and here comes the muddle if you are not watching
3.Click F2 you avoid the problem and the field is good for editing as the Status bar indicates as ‘Edit’