Software

Pro tip: Dismiss Excel's irksome Auto Fill Options list with these tricks

Learn how to reduce the annoyance factor of Excel's Auto Fill Options list.

 

HeadinHands_101613.png

  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.

Figure A

 

ExcelAutoFill_FigA_122313.JPG
 

Figure B

 

ExcelAutoFill_FigB_122313.JPG
 

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.

Avoiding

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.

Figure C

 

ExcelAutoFill_FigC_122313.JPG
 

Disabling

You can easily disable the options list by following these steps:

  1. 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.
  2. Choose Advanced in the left pane.
  3. 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.
  4. Click OK.

Figure D

 

ExcelAutoFill_FigD_122313.JPG
 

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.

Mouse tricks

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

Figure E

 

ExcelAutoFill_FigE_122313.JPG
 

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. 

 

 

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

10 comments
aksalaymeh
aksalaymeh

Thanks

Cool tip that we did not notce

gansell
gansell

Thanks again for the great tips :)

ased-tr
ased-tr

I just select the next empty cell, hit the spacebar, enter, then undo buttons and it's gone

michael.umehara
michael.umehara

I've found in 2010 that a single-click on either a row or column border will dismiss the pop-up. Another trick - go one or more cells further than needed, release mouse, grab handle and back up to the desired end of series - the pop-up goes away.

Glenn from Iowa
Glenn from Iowa

A handy tip: you can switch between Copy Cells and Fill Series by holding down the Ctrl key while you drag the fill handle. Especially helpful if you decide you want to disable the lists.

If you can overshoot your fill range by a cell or two, another method to get rid of the box is to fill a cell or two too much, release the mouse button, then drag the fill handle back by the extra cell or two. It clears the cells and gets rid of the list icon.

rcstan
rcstan

The easiest solution is to press Ctrl and then toggle the mouse wheel (forward & back), and it disappears in Excel 2003 & 2007.

awgiedawgie
awgiedawgie

But double-clicking a column border does change the column width in many cases.

Another simple mouse trick I've found quite handy is to double-click in any cell (as if you were going to edit its contents), and then click in a different cell to cancel the editing process (if you press Escape to cancel the editing, the Fill Options button reappears).

Blue_Oak
Blue_Oak

Or you could simply double-click the last cell in the range (as if you want to edit the last cell) and tap the Enter key.

Pesky auto-fill icon gone.

Glenn from Iowa
Glenn from Iowa

@rcstan Especially useful if, like me, you disabled editing directly in cells (double-clicking doesn't trigger a cell edit then).

RU7
RU7

This actually works if you double-click any cell in the range.  You can then cancel the edit without resurecting the fill box by clicking in any other cell.

I leave mine disabled though.  They cause me more hinderance than help.  I know where to find the functions in the rare case that I need them.