Select Page
Print Friendly, PDF & Email

Autofill and fill handle

Autofill and the fill handle are quick ways to repeat a formula following a pattern. There are two basic fill options: linear series and growth series.

Autofill a linear series

In a linear series, the next number in the series is found by adding a constant, or step value to the previous number, i.e. each subsequent number is incremented by the same value.

The easiest example of a linear series of numbers is 1, 2, 3, 4, 5.

A linear series can consist of decimals (1.5, 2.5, 3.5, 4.5), decreasing values (100, 98, 96, 94), or negative numbers (-1,-2,-3,-4), but in each case, we add (or subtract) the constant or step value.

In the example:

 

  1. The linear sequence is started by entering 1 and 2 (figure 1).
  2. Click the cursor on the black square at the bottom right (Fill Handle) then drag down as far as you want Excel to autofill.
  3. Release the mouse button and Excel will fill the column with the linear series (figure 2).

Autofill a growth series (geometric pattern)

The fastest way to specify a growth series is to enter the first two numbers and then right-click on the Fill Handle.

In this example, the common ratio is 2 (i.e. the next value is found by multiplying the last value by 2).

 

In an Excel worksheet, enter 1 in the first cell and 2 in the cell immediately below. See figure 1.

  1. Highlight the two cells and hover the cursor over the Fill Handle.
  2. Press the RIGHT mouse button, drag down the column, and release. The menu shown in figure 2 will appear. Click Growth Trend.
  3. Because you entered two numbers, Excel recognises that the multiplier is 2. The autofill results are shown in figure 3.

You must enter two numbers to use this quick method. If you only enter one number, the “Growth Trend” option is greyed out.

For more information see Autofill for dates and other non-numerical data.

The following video covers using the fill handle and absolute cell references.