Tip of the week # 467
25 November 2015

Display the Auto Fill Options on the fly (Microsoft Excel) *

Back in 2003 (not a typo; see tip_22.html) I started my quest to make you fall in love with Excel's Auto Fill handle (the small black square in the lower-right corner of a cell) to quickly create a list of months, days or dates, fill formulas or repeat values into adjacent cells.

Some of you might be using the Auto Fill Options button that appears just below your filled series, so that you can choose how the selection is filled. For example, when you drag a single number you can choose Fill Series to increase the number by one. But did you know there is a different way to display the list of relevant options?

Here's how:

  1. Select the cell that contains the data or formula that you want to fill into adjacent cells.
  2. Right-click and drag the fill handle across the cells that you want to fill.

A shortcut menu appears, listing the options depending on the content in the cell you selected (text or numbers) and on the format of the data. The Auto Fill Options button will not be displayed when you use this method. (With thanks to Leon for the tip inspiration!.)

* Unless stated otherwise, these tips were written for Microsoft Office 2010.