Microsoft Excel

Create series with AutoFill

Worksheets often use text series (such as January, February, March; or Sunday, Monday, Tuesday) and numeric series (such as 1, 3, 5; or 2003, 2004, 2005). Instead of entering these series by hand, you can use the fill handle to create them automatically. This handy feature is called AutoFill. The following steps show you how it works:

  • For a text series, select the first cell of the range you want to use, and enter the initial value. For a numeric series, enter the first two values and then select both cells.
  • Position the mouse pointer over the fill handle. The pointer changes to a plus sign (Fill Handle).
  • Click and drag the mouse pointer until the gray border encompasses the range you want to fill. If you're not sure where to stop, keep your eye on the pop-up value that appears near the mouse pointer and shows you the series value of the last selected cell.
  • Release the mouse button. Excel fills in the range with the series.

When you release the mouse button after using AutoFill, Excel not only fills in the series, but it also displays the Auto Fill Options smart tag Auto Fill Options. To see the options, move your mouse pointer over the smart tag and then click the downward-pointing arrow to drop down the list. The options you see depend on the type of series you created. However, you'll usually see at least the following four:

Copy Cells
Click this option to fill the range by copying the original cell or cells.

Fill Series
Click this option to get the default series fill.

Fill Formatting Only
Click this option to apply only the original cell's formatting to the selected range.

Fill Without Formatting
Click this option to fill the range with the series data but without the formatting of the original cell.

 Auto Fill Examples

This figure shows some series created with Fill Handle. Note that Excel increments any text value that includes a numeric component.

 Fill Handle Tips

Keep a few guidelines in mind when using the fill handle to create series:

  • Clicking and dragging the handle down or to the right increments the values. Clicking and dragging up or to the left decrements the values.
  • The fill handle recognizes standard abbreviations, such as Jan (January) and Sun (Sunday).
  • To vary the series interval for a text series, enter the first two values of the series and then select both of them before clicking and dragging. For example, entering 1st and 3rd produces the series 1st, 3rd, 5th, and so on.
  • If you use three or more numbers as the initial values for the fill handle series, Excel creates a "best fit" line.
by BrainBellupdated
Advertisement: