Excel AutoFill

The Excel Fill Handle

This tutorial is from Karyn Stille.  It is located at: http://tutorials.esmartweb.com/excel/autofill.htm.
She is an experienced software trainer who has helped thousands understand Microsoft Office. 
Her tutorials site and free newsletter get rave reviews from site visitors and subscribers.

Using AutoFill

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 in four directions.  AutoFill makes data entry fast and efficient.

Using AutoFill

To fill in a common series:

1. Select the initial cell for the series.

2. Place the mouse over the fill handle, as shown below.  Your mouse appears as a small black plus sign + (called crosshairs).

3. Drag the handle (click & hold the mouse button down) over the cells that are to be filled.  An AutoTip appears showing the impending result.

4. 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) down, right, left, or up.

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 down 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 right 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 same 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).  For more information on absolute cell references, see Excel Relative & Absolute Cell References.

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. A series can be copied in all directions - down, up, right, and left.