Excel: AutoFill Rules for Creating a Series

ID: Q89228

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.

REFERENCES

"Microsoft Excel User's Guide," version 5.0, pages 114-123 "Microsoft Excel User's Guide 1," version 4.0, pages 122-130

Additional query words: 4.00a drag


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 26, 1999