Excel can sometimes be annoying when it’s trying to be helpful. Its Auto Fill Options
list is a perfect example.
The Auto Fill Options list appears when you use the fill
handle (Figure A). To see the
options, click it (Figure B). In the
case of the example below, the default action is to continue the series. (The
default option and the list change, depending on the selected items.) The
options are self-explanatory, and it’s a useful tool.
It’s annoying because you can’t get rid of it, and it blocks
your view of other values. Many users complain about it but, so far, there’s no
intuitive way to dismiss it.
The most obvious action is to use it. You would think using
it would dismiss it, but it doesn’t. The next most obvious choice is to press
[Escape] — nice try, but that doesn’t work either. Fortunately, I’ve found some
not-so-intuitive mouse tricks that work. You can also avoid the list or disable
A little known click trick lets you avoid the list
altogether. Right-click the fill handle, and then drag the handle as you
normally would to complete the desired action (Figure C); this displays a contextual shortcut list. Choosing an
action from the list completes the pattern accordingly and dismisses the
shortcut list. The options list never appears!
Admittedly, this process is a bit awkward at first, and you
must remember it before using the
fill handle. It works, but unless you employ it often, you probably won’t
You can easily disable the options list by following these
- Click the File tab and choose Options in the
left pane. In Excel 2007, click the Office button and then click Excel Options.
In Excel 2003, choose Options from the Tools menu, click the Edit tab, and skip
to step 3.
- Choose Advanced in the left pane.
- In the Cut, Copy, and Paste section, uncheck
Show Paste Options Button When Content Is Pasted in Excel 2007 (Figure D). In
Excel 2003, uncheck Show Paste Options Buttons.
- Click OK.
This option disables both the fill and paste lists. There’s
no way to disable only the auto fill list of options. If you won’t miss either
list, this is a possible solution.
As a rule, I don’t like to disable default options; however,
it seems benign to disable these two options lists.
If you don’t want to disable the feature and remembering the
right-click on-the-fly technique isn’t working out for you, these two simple
mouse-click tricks will dismiss both of these options lists.
- Double-click any row or column header border.
Don’t change the row’s height or width — simply double-click the border.
- Click Zoom Out or Zoom In (in the bottom-right
corner of the screen) (Figure E).
Other actions will dismiss both lists, but these are the
most convenient that I’ve found. Regardless of where you’re working in the
sheet, one should be easy to implement without annoying you even more than the