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

Copy data, formulas, formatting, and more

Woman using laptop in office
Westend61 / Getty Images

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

Note: These instructions apply to Excel versions 2019, 2016, 2013, 2010, and Excel for Office 365.

Working with the Excel Fill Handle

Excel spreadsheet autofill options displayed

The fill handle works in conjunction with the mouse. 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 (+).

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

Copying Data Without Formatting

When data is copied with the fill handle, by default any formatting applied to the data is copied as well. To 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.

Screenshot of Excel showing formatting options

Clicking on the Auto Fill Options button opens a list of options that include:

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

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

Copying Formulas

Formulas being copied using the fill handle will update themselves to use data in their new location if they have been created using cell references.

Screenshot of Excel showing how to move a formula

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

cell references are used instead and the formula becomes:

=F1 + G1

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

Automatic Filling of 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.

Screenshot of Excel showing autofilling

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.

  5. Drag the fill handle down to cell D6.

  6. Cells D1 to D6 should contain the numbers: 2, 4, 6, 8, 10, 12.

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

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

To 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. A list can be added to the fill handle either by typing in the names manually or by copying them from an existing list in a worksheet.

Screenshot of Excel showing custom lists option

Typing the New AutoFill List Yourself

  1. Click on the File tab of the ribbon.

  2. Click on Options to bring up the Excel Options dialog box.

  3. Click the Advanced tab in the left-hand pane.

  4. Scroll to the General section of the options list in the right-hand pane.

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

  6. Type the new list in the List entries window.

  7. Click Add to add the new list to the Custom Lists window in the left-hand pane.

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

To Import a Custom AutoFill List From Your 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 A5.

  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$5 in the Import list from cells box at the bottom of the dialog box.

  4. Click the Import button.

  5. The new Auto Fill list appears in the Custom Lists window.

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