Excel: AutoFill Rules for Creating a Series

 Last reviewed: November 30, 1994
Article 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

KBCategory: kbusage
KBSubcategory:

Additional reference words: 4.00 4.00a 5.00 drag

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.