Microsoft Office Tutorials and References
In Depth Information
Tip 54: 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 54-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:
Figure 54-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 into cell A2. 2
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 in cell A2, the series will consist of odd integers: 1, 3, 5, 7, and so on.
When you release the mouse button after dragging, Excel displays an Auto Fill Options drop-down
list. Click to select other options. The list of options is particular helpful with dates. Figure 54-2 shows