Typing labels and values can be one of the more tedious tasks in creating a Microsoft Excel worksheet. For example, in making a budget, you often label cells with the names of the months. The Fill Series feature in Excel makes it easy to enter values into many cells with just a few clicks. You determine whether the cells should contain steadily increasing or decreasing values (based on linear or geometric changes), or values based on trends or numbers already found in cells. In this article, you will explore many options that make filling a series one of the most time-saving features in Excel.
Filling a Linear Series
In a linear series, each cell differs from the previous cell by a fixed amount. In Excel, the first value in the series is entered into the first cell. Next, you either select the cells that will hold the range, either across one row or down one column. Finally, display the Series dialog box, shown in Figure 1, by clicking the Edit menu, pointing to Fill, and clicking Series.
Figure 1: The Series dialog box
The Series dialog box contains four sections — the option buttons in the Series in section determine whether the fill will occur across a row or down a column. The Type section lets you choose how you want the cells to be filled. The Data unit section applies only when you choose Date as the series type. Finally, the bottom portion of the dialog box gives you the option of letting Excel base the series on values in the cells or the start and stop numbers you enter.
Say, for example, that you are preparing a worksheet that will project how much money you will have if you invested a certain amount every year. In the leftmost column you want each cell to display 1 through 10 representing years of investment. You could, of course, simple type each number. But what if you had to type the numbers 1 through 100 or 10 through 1000 counting by fives?
To begin the fill operation, you should type 1 into the first cell. Then you select that cell and the next 9 cells down. Finally, click Edit|Fill|Series. In the Series dialog box, the Columns option button is automatically selected since you have highlighted cells down from the starting value. The Linear button is selected as well. By default, the Step value is 1, meaning 1 will be added to the previous cell’s value for each selected cell. If you had not selected the cells first, you could enter 10 as the Stop value so the fill operation would end when a cell reached that number instead of when all the cells had been filled. In this case, the same result would be the same. Figure 2 shows the result of the fill operation.
Figure 2: The result of a linear fill
Using the same sample worksheet as above, imagine that you have already invested for two years. Beside the years 1 and 2 you enter the amount you invested. To project the amounts for the next eight years, you can ask Excel to figure out the amounts for the rest of the cells by clicking the Trend check box. When you turn on the Trend option, the Step and Stop boxes are no longer available. Instead, Excel will fill the selected boxes by calculating the average change between the values already entered and using that amount to add to or subtract from each previous cell in the series. Figure 3 shows how the increase from year 1 to year 2 is used to determine the increases for the rest of the years.
Figure 3: The result of a linear fill using the trend option
Watch It Grow
In a linear series, the step value is added to each cell’s previous cell. If you click the Growth option, each cell is multiplied by the step value. If you compared the graphs of a linear and growth series, you would see a straight line in the former and a curve in the growth type, as in Figure 4.
Figure 4: A linear vs. a growth series
In the example in Figure 4, an 8 percent increase was applied to each value in the series. (Entering 1.08 as the step value multiplies each cell by 108 percent.) If the same step value was used in a linear series, $1.08 would have been added to each cell, resulting in a very different, straight line series.
As with a linear series, you can check the Trend option in a growth series fill. In that case, Excel would calculate the average percentage change in the selected cells, and use that as the step value to be multiplied by.
Let’s Make a Date
Many series can be filled without even using the Series dialog box. Dragging the right handle of a cell with data causes Excel to apply AutoFill — an “intelligent” guess as to what values or labels should fill the series. In Figure 5, the month “January” was entered into cell B3. Then the bottom right handle of the cell was dragged to G3. As the handle was dragged across each cell, the fill label for that cell was temporarily displayed as a ScreenTip. When the mouse button was released, the months through “June” appeared automatically.
Figure 5: AutoFill created a series of dates
In Figure 6, several other series are filled using AutoFill. Note that in each case, only the data in the first cell was typed — the rest of the cells in the row were entered by Excel automatically.
Figure 6: Series created with AutoFill
Filling by Dragging and Other Options
AutoFilling is only one way to use dragging to fill a series. When you drag and release the button, the AutoFill Options icon immediately appears to the right of the pointer. If you click the icon, you see a set of option buttons. Below are the names and functions of these buttons:
- Copy Cells: Copies the data in the first cell to the rest of the cells.
- Fill Series: Applies AutoFill to the selected cells.
- Fill Formatting Only: Copies the format of the first cell — that is, the attributes the first cell has, such as color, font, size, alignment, etc.
- Fill Without Formatting: Applies AutoFill but not the formatting of the first cell to the selected cells.
- Series: Opens the Series dialog box, just as with the Edit|Fill|Series command.
Some options appear depending on the type of data in the first cell selected. For example, if the data is a date value or label, the following option buttons are offered:
- Fill Days: Continues the series by adding days to the first cell.
- Fill Workdays: Same as Fill Days, except the series skips weekend days.
- Fill Months: Continues the series by adding months to the first cell.
- Fill Years: Continues the series by adding years to the first cell.
If you select cells that contain two or more cells with numbers and right-drag the corner handle, you have the following two additional option buttons:
- Linear Trend: Continues the series by adding the average step value in the selected cells containing numbers.
- Growth Trend: Continues the series by multiplying the average step value in the selected cells containing numbers.
AutoFill can not only figure out the type of data with which to fill a series, it is also capable of determining how to continue a pattern. For example, if you select a series of cells that contain the numbers “1” and “3” in the first two cells, AutoFill will enter “5,” “7,” “9,” and so on in the rest of the selected cells. Likewise, typing “January,” “April,” AutoFill knows that your series should display every third month and continue the series with “July,” “October,” and so on. As you can see, filling a series in Excel can be a tremendous time saver.