Entering a Series of Labels, Numbers, and Dates
Excel streamlines the task of entering worksheet data by allowing you to fill a range of cells with one repeating value or a sequence of values, called a series. This capability saves you time when you're entering groups of labels, numbers, or dates in a report. For example, you can replicate the same price for many products in a report or create part numbers that increment predictably. To enter a series of values into a range of cells, you use the Fill command on the Insert menu or a mouse technique called AutoFill. The following sections show you how you can enter data automatically using these commands.
SEE ALSO
For information about replicating formulas in a worksheet, see "Replicating a Formula."
Using Autofill to Create a Series
The easiest method for entering repeating or incrementing data is to use Excel's AutoFill feature. This feature is activated when you drag a tiny black square called the fill handle over new cells. The fill handle is located in the lower right corner of the active cell or a selected range of cells, as shown in Figure 16-8. When you position the cell pointer over the fill handle, the cell pointer changes to a plus sign (+), indicating that the AutoFill feature is enabled. To create a series of labels, numbers, or dates, you drag the pointer over the cells you want to fill with information and then release the mouse button. (Notice that Excel shows the next value in the series in a pop-up box.) Like magic, you have a list of new values!
The AutoFill feature obeys a clear set of rules when it replicates data in cells, as shown in Table 16-1. When you drag the fill handle down or to the right, AutoFill creates values that increase based on the pattern in the range of cells you first select. When you drag the fill handle up or to the left, AutoFill creates values that decrease based on the pattern. If AutoFill doesn't recognize the pattern, it simply duplicates the selected cells.
Table 16-1. AutoFill Insertion Patterns
| Pattern Type | Series | Example |
|---|---|---|
| Label (Text) | No pattern, text is duplicated | Units, Units, Units |
| Number | Values increase based on pattern | 10, 20, 30 |
| Text with number | Series created by changing number based on pattern | Unit 1, Unit 2, Unit 3 |
| Day | Series created to match day format | Mon., Tues., Wed. |
| Month | Series created to match month format | Jan., Feb., Mar. |
| Year | Series created to match year format | 1998, 1999, 2000 |
| Time | Series created to match time interval | 1:30 PM, 2:00 PM, 2:30 PM |
TIP
To suppress the AutoFill feature (and just duplicate the selected cells), hold down the Ctrl key while you drag the fill handle.
Figure 16-8. Follow these steps to AutoFill a series of cells.
Using the Fill Commands
The mouse-driven AutoFill feature is designed to handle most of the data copying and replication in a worksheet, but you can also use a collection of Fill commands on the Edit menu to accomplish simple copying tasks. You'll find these commands useful if you want to copy one cell into many adjacent cells, or if you want to fine-tune the way patterns in an AutoFill series are created.
Filling Up, Down, Right, and Left
When you choose Fill from the Edit menu, a submenu appears that contains several replication commands, including Up, Down, Right, and Left. These commands let you copy information from one cell to a group of selected, adjacent cells. Figure 16-9 shows how the Fill Down command is used to copy the contents of cell A2 to cells A3 through A5. Note that cell comments are not copied when you use the Fill commands (because comments are not considered essential to the calculation process).
Figure 16-9. Select the range you want to fill, and choose Up, Down, Right, or Left from the Fill submenu.
The following steps show you how to fill a range using the Fill command:
- Place the cell pointer over the cell you want to replicate, and then drag the mouse over the cells you want to fill.
- Choose Fill from the Edit menu, and then choose the command from the Fill submenu corresponding to the direction you want to copy.
TIP
The Excel key combination for the Down command is Ctrl+D. The key combination for the Right command is Ctrl+R.
Using the Fill Series Dialog Box
If you want to specify a custom series, such as a number that increments in fractional portions or a maximum value for the series, select your fill range, and choose the Series command from the Edit menu's Fill submenu. The dialog box shown in Figure 16-10 appears. This dialog box allows you to specify the value type and date type—characteristics that are usually set automatically when you use the AutoFill feature.
Figure 16-10. The Series dialog box lets you create custom fill sequences.
What makes the Series dialog box handy is the Step Value and Stop Value text boxes, which let you control how the specified series increments and specify its final value. For example, if you want to increment a numeric series by 1.5, type 1.5 in the Step Value text box. Similarly, if you want to set 10 as the highest number in the series, type 10 in the Stop Value text box. Figure 16-11 shows the results you get when you start with the number 1 and use both these values in the Series dialog box. Notice that although cells A8, A9, and A10 were selected in the fill range (just as a guess), they were left empty because the stop value in the Series dialog box had been reached.
Figure 16-11. The Series command lets you increment by an amount you specify and stop when a limit you set has been reached.
