Days of the week sequences, months of the year, quarters, number patterns, formulas across a series of cells - are you manually typing all of these? Shame on you! Use AutoFill to fill in common sequences, number patterns, and formulas.
What is AutoFill?
AutoFill is a time saving tool that can automatically fill in a common series of words, number patterns, and formulas. AutoFill makes data entry fast and efficient.
Using AutoFill
To
fill in a common series:
Select the initial cell for the series.
Place the mouse over
the fill handle, as shown below. Your mouse appears as a
small black plus sign (called crosshairs). Drag the handle
(click & hold the mouse button down) over the cells that
are to be filled. An AutoTip appears showing the
impending result. Release the mouse
button. Excel fills in the cells as shown in the graphic
below. Note:
AutoFill can only be used to fill in cells in a contiguous range
(cells that are touching each other).
To fill in a number
pattern:
Follow the instructions
above, adjusting step 1 to selecting the first two cells in the
pattern. Selecting the first two cells before dragging the
fill handle allows Excel to examine the proper number pattern to
fill. In the example below, a number pattern is filled in
that counts by 5.
To fill in a formula:
Using the steps above,
select the cell with the formula you want to copy and drag the
fill handle to fill in the rest of the cells. This is an
excellent way to fill in a totals row. Excel automatically
adjusts/changes your cell references to create the new formulas. In some cases, you may
not want a particular cell reference to be changed by Excel when
using AutoFill. For example, if you are figuring the
commission for your sales staff for a particular month, and they
all have a commission rate of 10%, you may have the commission
rate in one cell. That cell should be referenced in all of
your formulas. As in the graphic below, you would always
want to reference cell B10 to calculate all of your formulas.
To do this, you need to use an absolute cell reference ($B$10).
Examples of Common AutoFill Series
A time series can include increments of days, weeks, or months that you specify, or it can include repeating sequences such as weekdays, month names, or quarters.
| Initial Selection | Extended Series |
| 9:00 | 10:00, 11:00, 12:00... |
| Mon | Tue, Wed, Thu... |
| Monday | Tuesday, Wednesday, Thursday... |
| Jan | Feb, Mar, Apr... |
| Jan-99, Apr-99 | Jul-99, Oct-99, Jan-00... |
| 15-Jan, 15-Apr | 15-Jul, 15-Oct, 15-Jan... |
| 1997, 1998 | 1999, 2000, 2001... |
| Qtr3 (or Q3 or Quarter3) | Qtr4, Qtr1, Qtr2... |
| text1, textA (see next example) | text2, textA, text3, textA... |
| Product 1, On backorder | Product 2, On backorder... |
| 1st Period | 2nd Period, 3rd Period... |
| Product 1 | Product 2, Product 3... |
| 1, 2 | 3, 4, 5, 6... |
| 1, 3 | 5, 7, 9, 11... |
Now, go forth and fill to your heart's content!