Page 84 - Excel Timesaving Techniques for Dummies

15_574272 ch13.qxd 10/1/04 10:39 PM Page 69
69
Getting Your Fill of AutoFill
The AutoFill Options button gives you access depressing the Ctrl key as you drag the Fill handle
to menu options that enable you to convert a prevents Excel from copying the number and forces
series in the fill range into copies of the initial it to generate a true sequentially numbered series.
value (and vice versa to convert copies into a
filled series) as well as to fill the range with for- If you, like me, routinely forget to hold down
matting only or to fill the series without copy- the Ctrl key and therefore end up with the
ing the formatting of the initial cell. To suppress same number copied into a range of cells,
the display of this button, deselect the Show select Fill Series on the pop-up menu attached
Paste Options check box on the Edit tab of the to the AutoFill Options button (which auto-
Options dialog box (Tools➪Options).
matically appears on the cell pointer’s Fill han-
dle as soon as you release the mouse button)
Using AutoFill to generate a sequentially to convert the copies into a bona-fide numeric
sequence, as shown in Figure 13-3.
numbered series

As you may have noticed in the last two examples in
Table 13-1, in initial entries that mix numbers and
text — as in 40 Mill Road — or entries whose num-
bers are entered as labels rather than values — as in
’00945 or L17-800 — Excel is really good at identify-
ing what number to increment.
As a result, you’d probably expect Excel to be an ace
when it comes to generating a sequentially num- • Figure 13-3: Converting a range filled with copies of the
bered series such as 1, 2, 3, 4, and the like (useful in same number into a sequential series.
numbering your data list records so that you can
refer to them and sort them by record number). Copying an entry instead of filling in a series
Unfortunately, AutoFill falls flat on its face (can a
software feature have a face?) when it comes to You can also use the Ctrl key to force Excel to copy
doing this. To prove it, all you have to do is enter the an initial entry in the cell range you drag through in
number 1 in any blank cell and then drag the Fill those situations where Excel would otherwise use
handle over just a few blank cells in columns to the the entry as the starting point for generating a
right or below to prove this point: Instead of generat- sequential series. Here, the association of the Ctrl
ing the simplest of sequentially numbered series key with copying in drag-and-drop operations makes
(1, 2, 3, 4 . . .), Excel just stupidly copies the number perfect sense (reinforced by the appearance of a tiny
1 to all the cells you drag through. plus sign that appears above and to the right of the
black cross mouse pointer).
Fortunately, Excel does provide a way to force
AutoFill (however reluctantly) to create a sequential Figure 13-4 illustrates how you can force Excel to
series from an initial value rather than just copying copy an initial entry in a cell range rather than use it
it everyplace you drag the Fill handle. The only prob- to generate a new series. In this new worksheet, I
lem is that it requires the use of the Ctrl key, which entered the column heading Module 5 in cell A2. To
is used in other mouse operations (cell drag-and- copy this heading across row 2 to the cell range
drop, for instance) to switch to making a copy of the B2:D2 (rather than generate the series Module 5,
selected cells or objects. In this case, however, Module 6, Module 7, and Module 8), I hold down the