Using AutoFill to Generate Sequentially Numbered Series

As you may have noticed in the last two examples in previous section Table, in initial entries that mix numbers and text - as in 40 Mill Road - or entries whose numbers are entered as labels rather than values - as in '00945 or L17-800 - Excel is really good at identifying what number to increment.

As a result, you'd probably expect Excel to be an ace when it comes to generating a sequentially numbered series such as 1, 2, 3, 4, and the like (useful in numbering your data list records so that you can refer to them and sort them by record number). Unfortunately, AutoFill falls flat on its face (can a software feature have a face?) when it comes to doing this. To prove it, all you have to do is enter the number 1 in any blank cell and then drag the Fill handle over just a few blank cells in columns to the right or below to prove this point: Instead of generating the simplest of sequentially numbered series (1, 2, 3, 4 . . .), Excel just stupidly copies the number 1 to all the cells you drag through.

Fortunately, Excel does provide a way to force AutoFill (however reluctantly) to create a sequential series from an initial value rather than just copying it everyplace you drag the Fill handle. The only problem is that it requires the use of the Ctrl key, which is used in other mouse operations (cell drag-anddrop, for instance) to switch to making a copy of the selected cells or objects. In this case, however, depressing the Ctrl key as you drag the Fill handle prevents Excel from copying the number and forces it to generate a true sequentially numbered series.

If you, like me, routinely forget to hold down the Ctrl key and therefore end up with the same number copied into a range of cells, select Fill Series on the pop-up menu attached to the AutoFill Options button (which automatically appears on the cell pointer's Fill handle as soon as you release the mouse button) to convert the copies into a bona-fide numeric sequence.