Microsoft KB Archive/89228

{|
 * width="100%"|

Excel: AutoFill Rules for Creating a Series

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0

-

SUMMARY
The AutoFill feature in Microsoft Excel can intelligently extend several types of data. This article discusses the rules that AutoFill follows in determining whether to fill a range with fixed values or to create a series based on the original data selected.

MORE INFORMATION
Note that in Microsoft Excel version 5.0, you can customize AutoFill by using a custom AutoFill list. For more information about using a custom list with the AutoFill feature, query on the following words in the Microsoft Knowledge Base:

  custom and list and autofill

RULE 1
If Microsoft Excel identifies a series in your source selection, AutoFill continues the series. If no series is identified, Microsoft Excel fills the AutoFill selection with the values in your source selection.

Example A - Rule 1
A1: 1

A2: 2

Selecting the range A1:A2 and dragging the fill handle to include the range A1:A4 results in the series:

A1: 1

A2: 2

A3: 3

A4: 4

Example B - Rule 1
A1: 1

A2: 1

Selecting cells A1:A2 and dragging the fill handle to include the range A1:A4 results in the filled selection since no series was identified in the selection:

A1: 1

A2: 1

A3: 1

A4: 1

RULE 2
AutoFill creates a series from a single number if the source selection is larger than one cell and the first or second cell contains text.

Example - Rule 2
A1: 1 B1: SALES

Selecting the range A1:B1 and dragging the fill handle to include the range A1:B4 results in:

A1: 1 B1: SALES

A2: 2 B2: SALES

A3: 3 B3: SALES

A4: 4 B4: SALES

Notice that in this case Microsoft Excel created a series from the single number in cell A1.

RULE 3
Microsoft Excel will not create or extend a series when the AutoFill selection runs perpendicular to the existing series; the series will only be created or extended in a row-wise or column-wise orientation.

Example A - Rule 3
A1: 1 B1: 2

Selecting the range A1:B1 and dragging the fill handle to include the range A1:B4 results in:

A1: 1 B1: 2

A2: 1 B2: 2

A3: 1 B3: 2

A4: 1 B4: 2

In this case Microsoft Excel didn't create a series because the existing series (1,2) ran row-wise while the AutoFill selection ran column-wise.

Example B - Rule 3
If it is desired to extend the series with 3 and 4 in A2:B2 and 5 and 6 in A3:B3, the series pattern would have to be created first as follows:

A1: 1 B1: 2

A2: 3 B2: 4

Selecting the range A1:B2 and dragging the fill handle to include the range A1:B4 results in:

A1: 1 B1: 2

A2: 3 B2: 4

A3: 5 B3: 6

A4: 7 B4: 8

While this is not a single series (1,2,3,4,5,6,7,8) but instead, two series: (1,3,5,7) and (2,4,6,8), it mimics the effect of creating a series that runs across and down.