14

Reproduce the issue

  1. Go to Excel and type fill in the first 2 rows of a column
  2. Select both rows of the column
  3. Drag down the anchor of the selection (bottom right) 1 row down

Now you will see this:

Stupid auto fill options button

My Question

How can I dismiss this button? I've tried Escape and moving to another cell but no go. Only thing I could do is type in something in another cell or save the worksheet. Both of these options are not ideal.

The menu itself is useful at times so don't want to outright disable it. Just want to know how to be able to dismiss and view the the data underneath it (B4 in this screenshot).

0

5 Answers 5

7
+25

This is indeed annoying behaviour. There are two workarounds I could find to get rid of the icon:

  • Click the line between any two row headings (as if you were about to resize the row). Providing you are careful not to drag, it will not resize the row (verifiable by the fact that nothing is added to the undo list) but the icon still disappears. From this article.

  • OR, I found that it dissapears if you hold Ctrl and scroll up then back down again to zoom the worksheet in and out. This way you can be sure you are not changing any data.

Otherwise I don't think there is a 'proper' way of dismissing it, but I am inclined to agree with Doug that dragging with a right-click is probably the best way of avoiding the need for the button in the first place.

3
  • That does work. The only drawback is that I'll always want to double-check that I didn't change the row height. +1 Nov 22, 2011 at 15:49
  • @DougGlancy: I found an alternative. If you hold Ctrl and scroll up then back down again it zooms the worksheet in and out which bizarrely seems to get rid of the icon.
    – James P
    Nov 23, 2011 at 15:50
  • James, I actually used your click-between-rows method a couple of times yesterday, and contrary to my stated fear, I didn't check the row height after. But this one is even better. And now that you've pointed the way, I see that doing something like Ctrl-B twice also clears it. Interestingly, switching to another worksheet and back doesn't. Nov 23, 2011 at 19:23
2

Good question! I've wondered about this but never looked into it until now. Sadly, I think the answer is "you can't."

There's a few workarounds offered, including the ones you mentioned, as well as turning off the feature and dragging with the Ctrl key down. However, I also find the "Fill Without Formatting" and other options useful, so don't want to turn it off.

The best answer I can find is to right-drag, i.e., right click and drag (hard, but doable, with a touchpad) and use the menu that pops up. I use to do this all the time in Excel 2003, so I think I could develop the habit of doing it only when I know it's going to be needed.

2

In Win7: -> Office button (top left) -> Excel options -> Advanced -> un-click "Show Paste Options button"

Might also work in Win2010

1
1

Point the button with the mouse and press esc. That works for me.

0

I make the column on the right hand side "Right Justified" temporarily so the options box doesn't sit on top of the data I am trying to read. Then I change it back when I am finished.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .