Page 85 - Excel Timesaving Techniques for Dummies

15_574272 ch13.qxd 10/1/04 10:39 PM Page 70
70
Technique 13: Speeding Up Data Entry with AutoFill
Ctrl key as I drag the Fill handle through this range. to increment the months by three units rather than
Note the presence of the plus sign above the black one.) With that out of the way, I select both cells A2
cross mouse pointer, here correctly indicating that and B2 and then drag the Fill handle to the right to
Excel will make a copy rather than generate a series. cell D2. As you can see from the ToolTip that
appears to the left of the black cross pointer in this
figure, Excel will enter Mar in cell D2. If I release the
mouse button at this point, Excel will generate the
series Jun, Sep, Dec, Mar in the cell range A2:D2.

• Figure 13-4: Pressing Ctrl while dragging the Fill handle
to force Excel to make copies rather than
generate a series.

• Figure 13-5: Generating a series that uses every third
You don’t have to press the Ctrl key before month as its increment.
you drag the range of cells. You can convert a
filled range into copies of the initial cell value Well, what about a sequential series that decreases
by pressing Ctrl after you’ve selected the fill
rather than increases (in other words, one that uses
range as long as it’s before you release the
mouse button. After that, you have to click the a negative increment)? To generate this kind of
series with AutoFill, all you have to do is enter the
AutoFill Options button and select Copy Cells
on its pop-up menu to make this conversion. larger value in the first cell of the range to be filled,
the smaller value (the one that exemplifies the nega-
tive increment) in the next cell (either in the cell in
Incrementally Speaking the column to the right if you want to generate the
series across the row or in the row below if you want
to generate it down the column). Select both cells —
As you may have noticed, whenever Excel gener- the one with the entry showing the starting value
ates a series from an initial entry, it automatically
and the next one showing the amount of decrease —
increases the series by an increment of one (be it and then drag the Fill handle to extend the series as
by one day, one month, one hour, one minute, one
far as you want to go.
widget, you name it). Fortunately, the AutoFill feature
is very teachable so that you aren’t stuck with always For example, suppose I want to create a numerical
generating a series that increases the base value by series going down column A — starting in cell A2 with
one. As long as you provide at least two entries that a value of 1000 — that decreases by 100 units in each
exemplify how many units Excel is to increase (or cell below. To generate this decreasing series, you
decrease) the entries in the series, you can generate enter 1000 in cell A2 and 900 in cell A3. Then select
series with almost any kind of increments. the range A2:A3 before you drag the Fill handle down
the rows of the column. Excel then enters 800 in cell
Figure 13-5 illustrates how you generate a series that
A4, 700 in cell A5, 600 in cell A6, and so on. (Note
uses an increment other than one unit. In this exam- that when you select the cells that demonstrate the
ple, the increment is three, so Excel generates a
increment to use, Excel knows right away that you
series that enters every third monthbeginnig with want to generate a series, even when your entries
June. To do this, I have to enter two samples: Jun in
are purely numerical.)
cell A2 and Sep in cell B2. (This gives Excel the idea