Learn how to reduce the annoyance factor of Excel's Auto Fill Options list.
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 it.
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 remember it.
You can easily disable the options list by following these steps:
- 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 lists.