Create, change, or delete a custom fill series

This topic is about automatically entering data or data in a series. For information about entering data manually, see Enter data in worksheet cells.

What do you want to do?

If the first few characters that you type in a cell match an existing entry in that column, Microsoft Excel automatically enters the remaining characters for you. Excel automatically completes only those entries that contain text or a combination of text and numbers. Entries that contain only numbers, dates, or times are not completed.

Do one of the following:

  • To accept the proposed entry, press ENTER.

The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entry.

  • To replace the automatically entered characters, continue typing.
  • To delete the automatically entered characters, press BACKSPACE.

 Notes 

Top of Page Top of Page

Use the fill handle to fill data

You can use the Series command (point to Fill on the Edit menu, and then click Series) to fill data into worksheet cells. You can also have Excel automatically continue a series of numbers, number and text combinations, dates, or time periods, based on a pattern that you establish. However, to quickly fill in several types of data series, you can select cells and drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Selected cell with fill handle.

The fill handle is displayed by default, but you can hide it.

  1. On the Tools menu, click Options.
  2. On the Edit tab, do one of the following:
    • To hide the fill handle, clear the Allow cell drag and drop check box.
    • To display the fill handle, select the Allow cell drag and drop check box.

To avoid replacing existing data when you drag the fill handle, make sure that the Alert before overwriting cells check box is selected. If you don't want to get a message about overwriting nonblank cells, you can clear this check box.

 Note   After you drag the fill handle, the Auto Fill Options button Button image appears so that you can choose how the selection is filled. For example, you can choose to fill just cell formats by clicking Fill Formatting Only, or you can choose to fill just the contents of a cell by clicking Fill Without Formatting. If you don't want to display the Auto Fill Options button every time you drag the fill handle, you can turn it off. On the Tools menu, click Options, click the Edit tab, and then clear the Show Paste Options buttons check box.

Top of Page Top of Page

Fill data into adjacent cells

  1. Select the cells that contain the data that you want to fill into adjacent cells.
  2. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Fill handle across the cells that you want to fill.
  3. You can use the Auto Fill Options button Button image, which appears after you drag the fill handle, to choose how to fill the selection. For example, you can choose Fill Formatting Only or Fill Without Formatting.

 Notes 

  • You can also fill the active cell with the contents of the cell above it. Point to Fill on the Edit menu, and then click Down (or press CTRL+D). To fill the active cell with contents of the cell to the left, point to Fill on the Edit menu, and then click Right (or press CTRL+R).
  • If you drag the fill handle up or to the left of a selection and stop in the selected cells without going past the first column or the top row in the selection, Excel deletes the data within the selection. You must drag the fill handle out of the selected area before releasing the mouse button.

Top of Page Top of Page

Fill formulas into adjacent cells

  1. Select the cell that contains the formula that you want to fill into adjacent cells.
  2. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Fill handle across the cells that you want to fill.
  3. You can use the Auto Fill Options button Button image, which appears after you drag the fill handle, to choose how to fill the selection. For example, you can choose Fill Formatting Only or Fill Without Formatting.

 Note   You can also fill the active cell with the contents of the cell above it. Point to Fill on the Edit menu, and then click Down (or press CTRL+D). To fill the active cell with the contents of the cell to the left, point to Fill on the Edit menu, and then click Right (or press CTRL+R).

Tip    You can automatically fill a formula downward, for all adjacent cells that it applies to, by double-clicking the fill handle of the first cell that contains the formula. For example, you have numbers in cells A1:A15 and B1:B15, and you type the formula =A1+B1 into cell C1. To copy that formula into cells C2:C15, select cell C1 and double-click the fill handle.

Top of Page Top of Page

Fill in a series of numbers, dates, or other built-in series items

Using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.), you can quickly fill cells in a range with a series of numbers or dates or with a built-in series for days, weekdays, months, or years.

  1. Select the first cell in the range that you want to fill.
  2. Type the starting value for the series.
  3. Type a value in the next cell to establish a pattern.

For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4. If you want the series 2, 2, 2, 2..., you can leave the second cell blank.

ShowMore examples of series that you can fill

When you fill a series, the selections are extended as shown in the following table. Items separated by commas are in placed in individual adjacent cells.

Initial selection Extended series
1, 2, 3 4, 5, 6,...
9:00 10:00, 11:00, 12:00,...
Mon Tue, Wed, Thu,...
Monday Tuesday, Wednesday, Thursday,...
Jan Feb, Mar, Apr,...
Jan, Apr Jul, Oct, Jan,...
Jan-99, Apr-99 Jul-99, Oct-99, Jan-00,...
15-Jan, 15-Apr 15-Jul, 15-Oct,...
1999, 2000 2001, 2002, 2003,...
1-Jan, 1-Mar 1-May, 1-Jul, 1-Sep,...
Qtr3 (or Q3 or Quarter3) Qtr4, Qtr1, Qtr2,...
text1, textA text2, textA, text3, textA,...
1st Period 2nd Period, 3rd Period,...
Product 1 Product 2, Product 3,...
  1. Select the cell or cells that contain the starting values.
  2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.

To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.

Tips

  • You can also specify the type of series by using the right mouse button to drag the fill handle over the range and then clicking the appropriate command on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.). For example, if the starting value is the date JAN-2002, click Fill Months for the series FEB-2002, MAR-2002, and so on; or click Fill Years for the series JAN-2003, JAN-2004, and so on.
  • If the selection contains numbers, you can control the type of series that you want to create. On the Edit menu, point to Fill, and then click Series. Under Type, specify the options that you want to use. A Linear series is calculated by adding the value in the Step Value box to each cell value in turn. A Growth series is calculated by multiplying the value in the Step Value box by each cell value in turn. A Date series fills date values incrementally by the value in the Step value box and dependent on the unit specified under Date unit. An Auto Fill series produces the same results as dragging the fill handle.
  • You can suppress Auto Fill by holding down CTRL while you drag the fill handle. The selected values are then copied to the adjacent cells, and Excel does not extend a series.

Top of Page Top of Page

Fill data by using a custom fill series

To make entering a particular sequence of data (such as a list of names or sales regions) easier, you can create a custom fill series. A custom fill series can be based on a list of existing items on a worksheet, or you can type the list from scratch.

 Use a custom fill series based on an existing list of items    

  1. On the worksheet, select the list of items that you want to use in the fill series.
  2. On the Tools menu, click Options, and then click the Custom Lists tab.
  3. Verify that the list of items that you selected is displayed in the Import list from cells box, and then click Import.

The items in the list that you selected are added to the Custom lists box.

  1. On the worksheet, click a cell, and then type the item in the custom fill series that you want to use to start the list.
  2. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Selected cell with fill handle across the cells that you want to fill.

 Use a custom fill series based on a new list of items    

  1. On the Tools menu, click Options, and then click the Custom Lists tab.
  2. In the Custom lists box, click New list, and then type the entries in the List entries box, beginning with the first entry.

Press ENTER after each entry.

  1. When the list is complete, click Add.
  2. On the worksheet, click a cell, and then type the item in the custom fill series that you want to use to start the list.
  3. Drag the fill handle Selected cell with fill handle across the cells that you want to fill.

 Notes 

  • A custom list can contain text or text mixed with numbers. To create a custom list that contains only numbers, such as 0 through 100, first select enough empty cells to contain the list. On the Format menu, click Cells, and then click the Number tab. Apply the Text format to empty cells, and then type the list of numbers in the formatted cells. Select the list and then import the list.
  • You cannot edit or delete a built-in fill series (such as a fill series for months and days), but you can edit or delete a custom fill series. On the Tools menu, click Options, and then click the Custom Lists tab. In the Custom lists box, select the list that you want to edit or delete. To edit the fill series, make the changes that you want in the List entries box, and then click Add. To delete the fill series, click Delete.

Top of Page Top of Page

 
 
Applies to:
Excel 2003