How to Copy Formulas and Data With Excel's Fill Handle

Copy data, formulas, formatting, and more

Excel's fill handle is a multipurpose, small black dot or square in the bottom right corner of the active cell that can save you time and effort when it is used to copy the contents of one or more cells to adjacent cells in a worksheet.

These instructions apply to Excel for Microsoft 365 and Excel versions 2019, 2016, 2013, and 2010.

Work With the Excel Fill Handle

The fill handle works in conjunction with the mouse. Fill handle uses include:

  • Copying data and formatting
  • Copying formulas
  • Filling cells with a series of numbers, such as odd or even numbers
  • Adding the days of week or month names to a worksheet
  • Adding custom lists of commonly used data, such as department names or report headings, to a worksheet

Try this easy example within your own Excel spreadsheet.

  1. Highlight the cell(s) containing the data to be copied or, in the case of a series, extended.

  2. Place the mouse pointer over the fill handle. The pointer changes to a small black plus sign (+).

    Excel spreadsheet autofill options displayed
  3. Press and hold down the left mouse button, then drag the fill handle to the destination cell(s).

Copy Data Without Formatting

When you copy data with the fill handle, by default, any formatting applied to the data is copied as well. You can copy data without copying the formatting. After copying data with the fill handle, Excel displays the Auto Fill Options button below and to the right of the newly filled cells.

Selecting the AutoFill Options button opens a list of options that include:

  • Copy cells
  • Fill formatting only
  • Fill without formatting
  • Flash fill

Selecting Fill without formatting will copy data with the fill handle but not the source formatting.

Copy Formulas

Formulas copied using the fill handle will automatically update to use data in their new location if you created them using cell references.

Cell references are the column letter and row number of the cell where the data used in the formula is located, such as A1 or D23. As an example:

Instead of entering the actual numbers into the formula in H1 to create this formula,

= 11 + 21

use cell references instead, and the formula becomes:

=F1 + G1

In both formulas, the answer in cell H1 is 32, but because the second formula uses cell references, you can copy it using the fill handle to cells H2 and H3, and it will give the correct result for the data in those rows.

Automatically Fill Cells

If Excel recognizes the cell contents as part of a series, it will auto-fill other selected cells with the next items in the series. To do so, you need to enter enough data to show Excel the pattern, such as counting by twos, that you want to use.

Here is a prime example of Excel's auto-fill feature:

  1. Type the number 2 in cell D1 and press the Enter key.

  2. Type the number 4 in cell D2 and press Enter.

  3. Select cells D1 and D2 to highlight them.

  4. Click and hold down the mouse pointer on the fill handle in the bottom right corner of the cell D2.

    D1 and D2 selected in Excel
  5. Drag the fill handle down to cell D6.

    Dragging fill handle down to D6 in Excel
  6. Cells D1 to D6 should contain the numbers: 2, 4, 6, 8, 10, 12.

    Filled cells in column D using fill handle

Add Preset Content to Cells

Excel also has preset lists of names, days of the week, and months of the year, that can be added to a worksheet using the fill handle. As an example, here is how you might add the days of the week within your worksheet.

Excel showing autofill with days of the week

Excel also contains a pre-set list of the short forms for the days of the week such as Sun, Mon, etc. as well as both full and short month names — January, February, March, and Jan, Feb, Mar that can be added to a worksheet using the steps listed.

  1. Type Sunday into cell A1.

  2. Press the Enter key on the keyboard.

  3. Click on the cell A1 again to make it the active cell.

  4. Place the mouse pointer over the fill handle in the bottom right corner of the active cell.

  5. The mouse pointer will change to a small black plus sign (+) when you have it over the fill handle.

  6. When the mouse pointer changes to the plus sign, click and hold down the mouse button.

  7. Drag the fill handle to cell G1 to auto-fill the days of the week from Monday to Saturday.

Add a Custom List to the Fill Handle

Excel also allows you to add your own lists of names such as department names or worksheet headings for use with the fill handle. You can add a list to the fill handle by typing in the names manually or by copying them from an existing list in a worksheet.

Excel custom lists option
  1. Select the File tab of the ribbon.

  2. Select Options to bring up the Excel Options dialog box.

    Options on File tab in Excel
  3. Select the Advanced tab in the left-hand pane.

    Advanced tab of Excel Options
  4. Scroll to the General section of the options list in the right-hand pane.

  5. Select the Edit Custom List button in the right-hand pane to open the Custom List dialog box.

    Edit Custom Lists button in Excel Options
  6. Type the new list in the List entries window.

    Custom list entries in Excel Custom List dialog box
  7. Select Add to add the new list to the Custom Lists window in the left-hand pane.

  8. Select OK twice to close all dialog boxes and return to the worksheet.

Import a Custom AutoFill List From a Spreadsheet

If you are looking to import custom autofill lists based on the content on your spreadsheets, follow these steps for a more hands-on approach:

  1. Highlight the range of cells in the worksheet containing the list elements, such as A1 to A7.

    Custom list in Excel worksheet column
  2. Follow steps 1 to 5 above to open the Custom List dialog box.

  3. The range of cells previously selected should be present in the form of absolute cell references, such as $A$1:$A$7 in the Import list from cells box at the bottom of the dialog box.

    Range displayed in Import list from cells box in Custom Lists dialog in Excel
  4. Select the Import button.

  5. The new AutoFill list appears in the Custom Lists window.

  6. Select OK twice to close all dialog boxes and return to the worksheet.

Was this page helpful?