Do you find that some dialogs like the Name Manager and the Data Validation dialog mess you around when you edit or enter formulas? I call this the Excel Dialog Box Muddle!
For years and years now these two dialogs change to Point mode by default the moment you start editing in the ‘Refers to’ line in the case of Name Manager and ‘Source’ in the case of the Data Validation dialog. So cell references start appearing in the middle of what you want in there and which is way too small anyway and it all gets muddled and most times you probably hit cancel and start again. The dialogs are not large windows in the first place so this unhappy, long standing default, adds to the general discomfort in working with them. Maybe there is more than these two dialogs, but I work in these two day in and day out, so they are the ones that annoy me the most.
The simple trick to stopping this problem 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’
