Friday, January 12, 2018

Fill data automatically in worksheet cells

Fill data automatically in worksheet cells

Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells.

This article does not explain how to enter data manually or enter data simultaneously on multiple worksheets. For information about how to manually enter data, see the article Enter data manually in worksheet cells.

Automatically repeat values that already exist in the column

If the first few characters that you type in a cell match an existing entry in that column, 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 automatically completed.

After Excel completes what you started typing, do one of the following:

  • To accept a 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.

Note: 

  • Excel completes an entry only when the cursor is at the end of the current cell contents.

  • Excel bases the list of potential AutoComplete entries on the column that contains the active cell. Entries that are repeated within a row are not automatically completed.

Turn automatic completion of cell values on or off

If you don't want Excel to automatically complete cell values, you can turn off this feature.

  1. Click the File tab, and then click Options.

  2. Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn automatic completion of cell values on or off.

Fill data into adjacent cells by using the fill handle

To quickly fill in several types of data series, you can select cells and drag the fill handle Fill handle . To use the fill handle, you select the cells that you want to use as a basis for filling additional cells, and then drag the fill handle across or down the cells that you want to fill.

By default, the fill handle is displayed, but you can hide it, or show it if it's hidden.

After you drag the fill handle, the Auto Fill Options button is displayed. If you don't want to display the Auto Fill Options button every time that you drag the fill handle, you can turn it off. Likewise, if the Auto Fill Options button does not appear when you drag the fill handle, you might want to turn it on.

Show or hide the fill handle

  1. Click the File tab, and then click Options.

  2. Click Advanced, and then under Editing options, select or clear the Enable fill handle and cell drag-and-drop check box to show or hide the fill handle.

  3. 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 see a message about overwriting nonblank cells, you can clear this check box.

Drag the fill handle to 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 across the cells that you want to fill.

  3. To change how you want to fill the selection, click Auto Fill Options Button image , and then click the option that you want.

    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.

Note: 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.

Turn Auto Fill Options on or off

  1. Click the File tab, and then click Options.

  2. Click Advanced, and then under Cut, copy, and paste, select or clear the Show Paste Options button when content is pasted check box to turn this option on or off.

Fill data into adjacent cells by using the Fill command

You can use the Fill command to fill the active cell or a selected range with the contents of an adjacent cell or range.

  1. Do one of the following:

    • To fill the active cell with the contents of an adjacent cell, select an empty cell that is below, to the right, above, or to the left of the cell that contains the data that you want to use to fill the empty cell.

    • To fill multiple adjacent cells, select the cell with the contents you want to fill and the adjacent cells into which you want to fill the content.

  2. On the Home tab, in the Editing group, click Fill, and then click Down, Right, Up, or Left.

Excel  Ribbon Image

Keyboard shortcut    To quickly fill a cell with the contents of an adjacent cell, you can press Ctrl+D to fill from the cell above or Ctrl+R to fill from the cell to the left.

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 across the cells that you want to fill.

  3. To choose how you want to fill the selection, click Auto Fill Options Button image , and then click the option that you want.

    Note: If automatic workbook calculation is not enabled, formulas will not recalculate when you fill cells. To check your workbook calculation options, do the following:

  4. Click the File tab.

  5. Under Excel, click Options, and then click the Formulas category.

  6. Under Calculation options, look under Workbook Calculation.

    • Automatic    Formulas automatically recalculate.

    • Automatic except for data tables    Formulas recalculate, unless the formula is in a data table.

    • Manual    Formulas never recalculate automatically.

    • Recalculate workbook before saving    This option is available only if Workbook Calculation is set to Manual. If this check box is selected, formulas do not automatically recalculate until you save the workbook. Note that several other actions can cause the workbook to be saved, such as using the Send To command.

      Notes: 

      • You can also fill the active cell with the formula of an adjacent cell by using the Fill command (on the Home tab in the Editing group), or by pressing Ctrl+D to fill a cell below or Ctrl+R to fill a cell to the right of the cell containing the formula.

      • 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, cells A1:A15 and B1:B15 contain numbers, and you type the formula =A1+B1 in cell C1. To copy that formula into cells C2:C15, select cell C1 and double-click the fill handle.

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

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 by using the fill handle or the Fill command.

Fill cells with a series by using the fill handle

  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.

  4. Select the cell or cells that contain the starting values.

  5. Drag the fill handle 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.

    Note: 

  6. 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. For example, if the starting value is the date JAN-2007, click Fill Months for the series FEB-2007, MAR-2007, and so on; or click Fill Years for the series JAN-2007, JAN-2008, and so on.

  7. If the selection contains numbers, you can control the type of series that you want to create.

  8. You can suppress series AutoFill by holding down Ctrl as you drag the fill handle of a selection of two or more cells. The selected values are then copied to the adjacent cells, and Excel does not extend a series.

Fill cells with a series by using the Fill command

  1. Select the first cell in the range that you want to fill.

  2. Type the starting value for the series.

  3. On the Home tab, in the Editing group, click Fill, and then click Series.

    Excel  Ribbon Image

  4. Under Type, click one of the following options:

    • Linear    Creates a series that is calculated by adding the value in the Step value box to each cell value in turn.

    • Growth    Creates a series that is calculated by multiplying the value in the Step value box by each cell value in turn.

    • Date    Creates a series that fills date values incrementally by the value in the Step value box and is dependent on the unit specified under Date unit.

    • AutoFill    Creates a series that produces the same results as dragging the fill handle.

  5. To establish a pattern for the series, in the Step value and Stop value boxes, type the values you want.

Examples of series that you can fill

When you fill a series, the selections are extended as shown in the following table. In this table, items that are separated by commas are contained in individual adjacent cells on the worksheet.

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-07, Apr-07

Jul-07, Oct-07, Jan-08,...

15-Jan, 15-Apr

15-Jul, 15-Oct,...

2007, 2008

2009, 2010, 2011,...

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,...

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. 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.

Note: A custom list can only contain text or text mixed with numbers.

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. Click the File tab, and then click Options.

  3. Click Advanced, and then under General, click the Edit Custom Lists button.

  4. Verify that the cell reference of 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.

  5. Click OK twice.

  6. 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.

  7. Drag the fill handle Fill handle across the cells that you want to fill.

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

  1. Click the File tab, and then click Options.

  2. Click Advanced, and then under General, click Edit Custom Lists.

  3. In the Custom lists box, click NEW LIST, and then type the entries in the List entries box, starting with the first entry.

  4. Press Enter after each entry.

  5. When the list is complete, click Add, and then click OK twice.

  6. 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.

  7. Drag the fill handle Fill handle across the cells that you want to fill.

Edit or delete a custom fill series

  1. Click the File tab, and then click Options.

  2. Click Advanced category, and then under General, click Edit Custom Lists.

  3. In the Custom lists box, select the list that you want to edit or delete, and then do one of the following:

    • 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.

This article does not explain how to enter data manually or enter data simultaneously on multiple worksheets. For information about how to manually enter data, see the article Enter data manually in worksheet cells.

Automatically repeat values that already exist in the column

If the first few characters that you type in a cell match an existing entry in that column, 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 automatically completed.

After Excel completes what you started typing, do one of the following:

  • To accept a 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: 

  • Excel completes an entry only when the cursor is at the end of the current cell contents.

  • Excel bases the list of potential AutoComplete entries on the column that contains the active cell. Entries that are repeated within a row are not automatically completed.

Turn off automatic completion of cell values

If you don't want Excel to automatically complete cell values, you can turn off this feature.

  1. Click the File tab.

  2. Under Excel, click Options.

  3. Click Advanced, and then under Editing options, clear or select the Enable AutoComplete for cell values check box to turn automatic completion of cell values on or off.

Use the fill handle to fill data

To quickly fill in several types of data series, you can select cells and drag the fill handle Fill handle . To use the fill handle, you select the cells that you want to use as a basis for filling additional cells, and then drag the fill handle across or down the cells that you want to fill.

Hide or display the fill handle

By default, the fill handle is displayed, but you can hide it, or show it if it's hidden.

  1. Click the File tab.

  2. Under Excel, click Options.

  3. Click Advanced, and then under Editing options, clear or select the Enable fill handle and cell drag-and-drop check box to hide or show the fill handle.

  4. 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 see a message about overwriting nonblank cells, you can clear this check box.

Use Auto Fill Options to change how the selection is filled

After you drag the fill handle, the Auto Fill Options button Button image appears so that you can change 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.

Turn Auto Fill Options on or off

If you don't want to display the Auto Fill Options button every time that you drag the fill handle, you can turn it off. Likewise, if the Auto Fill Options button does not appear when you drag the fill handle, you might want to turn it on.

  1. Click the File tab.

  2. Under Excel, click Options.

  3. Click Advanced, and then under Cut, copy, and paste, clear the Show Paste Options button when content is pasted check box.

Fill data into adjacent cells by using the Fill command

You can use the Fill command to fill the active cell or a selected range with the contents of an adjacent cell or range, or you can quickly fill adjacent cells by dragging the fill handle Fill handle .

Fill the active cell with the contents of an adjacent cell

  1. Select an empty cell that is below, to the right, above, or to the left of the cell that contains the data that you want to use to fill the empty cell.

  2. On the Home tab, in the Editing group, click Fill, and then click Down, Right, Up, or Left.

Excel  Ribbon Image

Tip: To quickly fill a cell with the contents of an adjacent cell, you can press CTRL+D to fill from the cell above or CTRL+R to fill from the cell to the left. To fill a cell with the contents of a cell below it (that is, to fill up), on the Home tab, in the Editing group, click Fill, and then click Up. To fill a cell with the contents of a cell to the right (fill left), on the Home tab, in the Editing group, click Fill, and then click Left.

Drag the fill handle to 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 across the cells that you want to fill.

  3. To change how you want to fill the selection, click Auto Fill Options Button image , and then click the option that you want.

Note: 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.

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 across the cells that you want to fill.

  3. To choose how you want to fill the selection, click Auto Fill Options Button image , and then click the option that you want.

    Note: If automatic workbook calculation is not enabled, formulas will not recalculate when you fill cells. To check your workbook calculation options, do the following:

  4. Click the File tab.

  5. Under Excel, click Options, and then click the Formulas category.

  6. Under Calculation options, look under Workbook Calculation.

    • Automatic    Formulas automatically recalculate.

    • Automatic except for data tables    Formulas recalculate, unless the formula is in a data table.

    • Manual    Formulas never recalculate automatically.

    • Recalculate workbook before saving    This option is available only if Workbook Calculation is set to Manual. If this check box is selected, formulas do not automatically recalculate until you save the workbook. Note that several other actions can cause the workbook to be saved, such as using the Send To command.

Tips

  • You can also fill the active cell with the formula of an adjacent cell by using the Fill command (on the Home tab in the Editing group), or by pressing CTRL+D to fill a cell below or CTRL+R to fill a cell to the right of the cell containing the formula.

  • 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, cells A1:A15 and B1:B15 contain numbers, and you type the formula =A1+B1 in cell C1. To copy that formula into cells C2:C15, select cell C1 and double-click the fill handle.

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

Using the fill handle, 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.

More examples of series that you can fill

When you fill a series, the selections are extended as shown in the following table. In this table, items that are separated by commas are contained in individual adjacent cells on the worksheet.

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-07, Apr-07

Jul-07, Oct-07, Jan-08,...

15-Jan, 15-Apr

15-Jul, 15-Oct,...

2007, 2008

2009, 2010, 2011,...

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 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. For example, if the starting value is the date JAN-2007, click Fill Months for the series FEB-2007, MAR-2007, and so on; or click Fill Years for the series JAN-2007, JAN-2008, and so on.

  • If the selection contains numbers, you can control the type of series that you want to create.

Fill cells with a series of numbers

  1. On the Home tab, in the Editing group, click Fill, and then click Series.

    Excel  Ribbon Image

  2. Under Type, click one of the following options:

    • Linear    Creates a series that is calculated by adding the value in the Step value box to each cell value in turn.

    • Growth    Creates a series that is calculated by multiplying the value in the Step value box by each cell value in turn.

    • Date    Creates a series that fills date values incrementally by the value in the Step value box and is dependent on the unit specified under Date unit.

    • AutoFill    Creates a series that produces the same results as dragging the fill handle.

  3. You can suppress series AutoFill by holding down CTRL as you drag the fill handle of a selection of two or more cells. The selected values are then copied to the adjacent cells, and Excel does not extend a series.

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. 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.

Note: A custom list can only contain text or text mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers that is formatted as text.

Format numbers as text

  1. Select enough cells for the list of numbers that you want to format as text.

  2. On the Home tab, in the Number group, click the arrow on the Number Format box, and then click Text.

Excel  Ribbon Image

Tip: You might have to click More Number Formats to see the Text format in the list.

  1. In the formatted cells, type the list of numbers.

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. Click the File tab.

  3. Under Excel, click Options.

  4. Click Advanced, and then under General, click Edit Custom Lists.

  5. Verify that the cell reference of 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.

  6. Click OK twice.

  7. 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.

  8. Drag the fill handle Fill handle across the cells that you want to fill.

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

  1. Click the File tab.

  2. Under Excel, click Options.

  3. Click Advanced, and then under General, click Edit Custom Lists.

  4. In the Custom lists box, click NEW LIST, and then type the entries in the List entries box, starting with the first entry.

  5. Press ENTER after each entry.

  6. When the list is complete, click Add, and then click OK twice.

  7. 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.

  8. Drag the fill handle Fill handle across the cells that you want to fill.

Edit or delete a custom fill series

  1. Click the File tab.

  2. Under Excel, click Options.

  3. Click Advanced category, and then under General, click Edit Custom Lists.

  4. In the Custom lists box, select the list that you want to edit or delete, and then do one of the following:

    • 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

Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells. This article does not explain how to enter data manually or enter data simultaneously on multiple worksheets. For information about how to manually enter data, see the article Enter data manually.

Automatically repeat values that already exist in the column

If the first few characters that you type in a cell match an existing entry in that column, 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 automatically completed.

After Excel completes what you started typing, do one of the following:

  • To accept a 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: 

  • Excel completes an entry only when the cursor is at the end of the current cell contents.

  • Excel bases the list of potential AutoComplete entries on the column that contains the active cell. Entries that are repeated within a row are not automatically completed.

Turn off automatic completion of cell values

If you don't want Excel to automatically complete cell values, you can turn off this feature.

  1. Click the Microsoft Office Button Office button image , and then click Excel Options.

  2. Click Advanced, and then under Editing options, clear or select the Enable AutoComplete for cell values check box to turn automatic completion of cell values on or off.

Use the fill handle to fill data

To quickly fill in several types of data series, you can select cells and drag the fill handle Fill handle . To use the fill handle, you select the cells that you want to use as a basis for filling additional cells, and then drag the fill handle across or down the cells that you want to fill.

Hide or display the fill handle

By default, the fill handle is displayed, but you can hide it, or show it if it's hidden.

  1. Click the Microsoft Office Button Office button image , and then click Excel Options.

  2. Click Advanced, and then under Editing options, clear or select the Enable Fill handle and cell drag-and-drop check box to hide or show the fill handle.

  3. 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 see a message about overwriting nonblank cells, you can clear this check box.

Use Auto Fill Options to change how the selection is filled

After you drag the fill handle, the Auto Fill Options button Button image appears so that you can change 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.

Turn Auto Fill Options on or off

If you don't want to display the Auto Fill Options button every time you drag the fill handle, you can turn it off. Likewise, if the Auto Fill Options button does not appear when you drag the fill handle, you might want to turn it on.

  1. Click the Microsoft Office Button Office button image , and then click Excel Options.

  2. Click Advanced, and then under Cut, Copy, and Paste, clear the Show Paste Options buttons check box.

Fill data into adjacent cells by using the Fill command

You can use the Fill command to fill the active cell or a selected range with the contents of an adjacent cell or range, or you can quickly fill adjacent cells by dragging the fill handle Fill handle .

Fill the active cell with the contents of an adjacent cell

  1. Select an empty cell that is below, to the right, above, or to the left of the cell that contains the data that you want to use to fill the empty cell.

  2. On the Home tab, in the Editing group, click Fill, and then click Down, Right, Up, or Left.

    Excel Ribbon Image

Tip: To quickly fill a cell with the contents of an adjacent cell, you can press CTRL+D to fill from the cell above or CTRL+R to fill from the cell to the left. To fill a cell with the contents of a cell below it (that is, to fill up), on the Home tab, in the Editing group, click Fill, and then click Up. To fill a cell with the contents of a cell to the right (fill left), on the Home tab, in the Editing group, click Fill, and then click Left.

Drag the fill handle to 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 across the cells that you want to fill.

  3. To change how you want to fill the selection, click Auto Fill Options Button image , and then click the option that you want.

Note: 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.

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 across the cells that you want to fill.

  3. To choose how you want to fill the selection, click Auto Fill Options Button image , and then click the option that you want.

Note: If automatic workbook calculation is not enabled, formulas will not recalculate when you fill cells. To check your workbook calculation options, do the following:

  1. Click the Microsoft Office Button Office button image , click Excel Options, and then click the Formulas category.

  2. Under Calculation options, look under Workbook Calculation.

    • Automatic    Formulas automatically recalculate.

    • Automatic except for data tables    Formulas recalculate, unless the formula is in a data table.

    • Manual    Formulas never recalculate automatically.

    • Recalculate workbook before saving    This option is available only if Workbook Calculation is set to Manual. If this check box is selected, formulas do not automatically recalculate until you save the workbook. Note that several other actions can cause the workbook to be saved, such as using the Send To command.

Tips

  • You can also fill the active cell with the formula of an adjacent cell by using the Fill command (on the Home tab in the Editing group) or by pressing CTRL+D to fill a cell below or CTRL+R to fill a cell to the right of the cell containing the formula.

  • 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, cells A1:A15 and B1:B15 contain numbers, and you type the formula =A1+B1 in cell C1. To copy that formula into cells C2:C15, select cell C1 and double-click the fill handle.

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

Using the fill handle, 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.

    More examples of series that you can fill

    When you fill a series, the selections are extended as shown in the following table. In this table, items that are separated by commas are contained in individual adjacent cells on the worksheet.

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-07, Apr-07

Jul-07, Oct-07, Jan-08,...

15-Jan, 15-Apr

15-Jul, 15-Oct,...

2007, 2008

2009, 2010, 2011,...

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 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. For example, if the starting value is the date JAN-2007, click Fill Months for the series FEB-2007, MAR-2007, and so on; or click Fill Years for the series JAN-2007, JAN-2008, and so on.

  • If the selection contains numbers, you can control the type of series that you want to create.

    Fill cells with a series of numbers

    1. On the Home tab, in the Editing group, click Fill, and then click Series.

      Excel Ribbon Image

    2. Under Type, click one of the following options:

      • Linear    Creates a series that is calculated by adding the value in the Step value box to each cell value in turn.

      • Growth    Creates a series that is calculated by multiplying the value in the Step value box by each cell value in turn.

      • Date    Creates a series that fills date values incrementally by the value in the Step value box and dependent on the unit specified under Date unit.

      • AutoFill    Creates a series that produces the same results as dragging the fill handle.

  • You can suppress series AutoFill by holding down CTRL as you drag the fill handle of a selection of two or more cells. The selected values are then copied to the adjacent cells, and Excel does not extend a series.

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. 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.

Note: A custom list can only contain text or text mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers that is formatted as text.

Format numbers as text

  1. Select enough cells for the list of numbers that you want to format as text.

  2. On the Home tab, in the Number group, click the arrow on the Number Format box, and then click Text.

    Excel Ribbon Image

    Tip: You might have to click More to see the Text format in the list.

  3. In the formatted cells, type the list of numbers.

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. Click the Microsoft Office Button Office button image , and then click Excel Options.

  3. Click Popular, and then under Top options for working with Excel, click Edit Custom Lists.

  4. Verify that the cell reference of 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.

  5. Click OK twice.

  6. 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.

  7. Drag the fill handle Fill handle across the cells that you want to fill.

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

  1. Click the Microsoft Office Button Office button image , and then click Excel Options.

  2. Click Popular, and then under Top options for working with Excel, click Edit Custom Lists.

  3. In the Custom lists box, click NEW LIST, and then type the entries in the List entries box, starting with the first entry.

    Press ENTER after each entry.

  4. When the list is complete, click Add, and then click OK twice.

  5. 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.

  6. Drag the fill handle Fill handle across the cells that you want to fill.

Edit or delete a custom fill series

  1. Click the Microsoft Office Button Office button image , and then click Excel Options.

  2. Click Popular category, and then under Top options for working with Excel, click Edit Custom Lists.

  3. In the Custom lists box, select the list that you want to edit or delete, and then do one of the following:

    • 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

Did you know?

If you don't have an Office 365 subscription or the latest Office version, you can try it now:

Try Office 365 or the latest version of Excel

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

No comments:

Post a Comment