Excel: Save Time With AutoFill

A recurrent theme in the world of science fiction is the free thinking machine or robot that can feel human emotions and cause terror to mankind. The child robot David in the 2001 film Artificial Intelligence saw Martin, the returning human son, as a rival and so tried to drown him; the murderous HAL in 2001 a Space Odyssey attempted to kill the astronauts who had been ordered to switch him off, and I remember in an early edition of Spider Man comic, the web-slinger was called into action to stop a robot that was running amok on campus.

There will always be a fascination in machines that can think for themselves and one day we may see the advent of a computer we can interact with as though it were another human. In the meantime we have many examples of computing that appears to think for itself, from computerised chess games that have been around for decades to the automatic functions in Excel. The latter are pretty much taken for granted today, but in the early days watching Excel predict what you were about to type must have seemed like the computer had a mind of its own.

Of course functions such as AutoFill are not examples of free thinking, but merely the computer doing what it has been programmed to do, and, as such, it is susceptible to mistakes made by the operator.

For example, if you wanted to number a column from 1-100 then a lot of time can be saved by simply typing 1 and 2 and then dragging the AutoFill handle (at the bottom right of the cell) down another 98 cells. However, if you make a slip with the mouse, or you are unfamiliar with how AutoFill works, and you select only the lower of the two cells, then your AutoFill will show only a number 2 in each cell. This is because it has not been given all of the information and it has no increment on which to base what it displays.

To avoid making this sort of mistake, it is possible to use AutoFill to fill the column as described above without giving it any increment and by typing the value 1 only. Simply insert your 1 into the desired cell and hold down the Ctrl key. Then when you drag the Autofill handle down you will see the following values appear. This shortcut will only display data in increments of 1, and it does not work with other predictable data, such as days of the week or months of the year, but then it doesn't need to.

Whereas with numbers you need to tell AutoFill the increment you want, days and months are generally used in the order they occur and so you need type only one and AutoFill will get the message. As is the case with functions on all Office applications, a lot can be learned by exploring AutoFill and finding out for yourself how it can save you time and keystrokes.

If you want to disable the AutoFill function for some reason, then this is how to do it. For pre-2007 versions of Excel, go to Tools/Options/Edit and deselect the tick box on Allow Cell Drag and Drop, then click OK.

For Excel 2007 click the Office button and select Excel Options. Click Advanced and in Editing Options deselect the Enable Fill Handle and Cell Drag-and-Drop tick box.

Having said that, I can see no reason to switch off such a great time-saver as the AutoFill function, other than the small handle on the bottom right corner may be distracting to a new user. So continue to use it and save time every day, and who knows, maybe the day will come when you will be training a robot that sits at your desk how to save time by using AutoFill.