Weekly Computer Tip # 370
6 June 2012


Stop AutoFill (Excel 2003-2010)

During a recent Excel training session I encouraged the course participants to think about what the favourite tip was they picked up so far. The usual "suspects" came up (F4, F11, AutoFill) but I was pleasantly surprised to hear "Not AutoFill". This delegate often wanted to repeat, say, Wed or Wednesday in a range of cells, so she did not like Excel's AutoFill feature which changes the single label "Wed" to the series "Wed, Thu, Fri, Sat, Sun," etc. Instead, she wanted it copied into adjacent cells. OK, you can use CTRL+C and CTRL+V to do that. Or perhaps you use CTRL+D? But as long as you don't ignore the Auto Fill Options "smart tag", which appears in the bottom right corner of the last cell you dragged the fill handle to, you can choose Copy Cells and hey presto!

Here's how:

  1. Enter the first entry (say, Wednesday) and then select the cell in which that information appears.
  2. Drag the fill handle (in the lower-right corner of the selection), and extend the selection until it includes the entire range that is to be filled.
  3. Click on the Auto Fill Options button.
  4. Select the Copy Cells option.

Or - bonus tip! - simply hold down your CTRL key while you autofill, say, Wed or June or 06-Jun. (As the result of an AutoFill operation is determined by the contents of the first cell , you can fill series by holding down the CTRL key when you drag the fill handle of a number, which will increment rather than copy.)