Entering Data Quickly with Auto Fill

Excel features a couple of tricks for quick data entry. It can copy an entry from one cell into as many additional cells as you like, or it can fill several cells with a series of entries, such as the months of a year or a series of numbers that increase incrementally (5, 10, 15, 20, 25...). You simply type one entry and then order Excel to fill in the rest.

The easiest way to fill a series of neighboring cells is to use the cell selector's fill handle . The fill handle appears as a tiny dark square in the lower-right corner of the selected cell. To use the fill handle to copy an entry into other cells or to fill neighboring cells with a series of entries, follow these steps:

1. Type your entry, or select the cell that contains the entry you want to copy or use as the first item in the series.

2. Move the mouse pointer over the fill handle so that the pointer appears as a cross.

3. Hold down the left mouse button and drag the fill handle up, down, left, or right to highlight the cells you want to fill, as shown in Figure 4.6.

   Figure 4.6. Drag the fill handle to highlight the cells you want to fill.

   

4. Release the mouse button. Excel fills the highlighted cells as follows and displays the Auto Fill Options button (below and to the right of the fill handle):

   If the source cell contains a number, Excel copies the number into the selected cells.

   If the source cell contains an entry typically used in a series, such as Jan or January, Mon or Monday, or the like, Excel fills the selected cells with the entries that complete the series. For example, if the original cell contained Jan and you filled it into five other cells, those cells would contain Feb, Mar, Apr, May, and Jun.

5. Click the Auto Fill Options button to display the available options, as shown in Figure 4.7. These options enable you to change the way Auto Fill completes the operation. For example, if you intend to copy a value into several cells and Auto Fill has filled in a series instead, you can click Copy Cells to have the series replaced with copies of the original entry.

   Figure 4.7. The Auto Fill button enables you to provide more specific instructions on how to complete the fill operation.

   

6. Click the desired Auto Fill option, or click outside the menu if Auto Fill performed the fill exactly as you had wanted.

Auto Fill obtains its information for creating series from lists of commonly used series. You can create your own custom Auto Fill lists. Here's what you do:

1. Open the Tools menu and choose Options . The Options dialog box appears.

2. Click the Custom Lists tab.

3. Click the Add button, as shown in Figure 4.8. The insertion point moves to the List Entries box.

   Figure 4.8. You can create your own Auto Fill lists.

   

4. Under List Entries , type the entries that comprise the list. After each entry, press Enter to separate the entries.

5. Click OK .

You can now fill this list into several cells by typing the first list item in a cell and then dragging the fill handle over additional cells.

Tip

Type the first two to three entries of a series (for example, 1, 3, 5). Drag over those entries to select them, and then drag the fill handle to select the cells in which you want the series to continue. When you release the mouse button, Excel completes the series for you.