Microsoft Office Tutorials and References

In Depth Information

**Automatically Filling a Range with a Series**

Automatically Filling a Range with a Series

If you need to fill a range with a series of values, one approach is to enter the first value, write

a formula to calculate the next value, and copy the formula. For example, Figure 35-1 shows a

series of consecutive numbers in column A. Cell A1 contains the value 1, and cell A2 contains this

formula, which was copied down the column:

=A1+1

Figure 35-1:
Excel offers an easy way to generate a series of values like these.

Another approach is to let Excel do the work by using the handy Autofill feature:

1.
Enter 1 into cell A1.

2.
Enter
2 into cell A2.

3.
Select A1:A2.

4.
Move the mouse cursor to the lower right corner of cell A2 (the cell’s
fill handle
) and when

the mouse pointer turns into a black plus sign, drag down the column to fill in the cells.

You can turn this behavior on and off. If cells don’t have a fill handle, choose File➜

Options and click the Advanced tab in the Excel Options dialog box. Select the check

box labeled Enable Fill Handle and Cell Drag-And-Drop.

The data entered in Steps 1 and 2 provide Excel with the information it needs to determine which

type of series to use. If you entered 3 into cell A2, the series would then consist of odd integers: 1,

3, 5, 7, and so on.

Here’s another Autofill trick: If the data you start with is irregular, Excel completes the Autofill

action by doing a linear regression and fills in the predicted values. Figure 35-2 shows a

worksheet with monthly sales values for January through July. If you use Autofill after selecting C2:C8,