Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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.

share|improve this answer
    
That does work. The only drawback is that I'll always want to double-check that I didn't change the row height. +1 –  Doug Glancy Nov 22 '11 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 Nov 23 '11 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. –  Doug Glancy Nov 23 '11 at 19:23

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.

share|improve this answer

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

Might also work in Win2010

share|improve this answer
    
The OP says he doesn't want to outright disable the menu. –  G-Man Oct 30 at 18:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.