Double Click the Fill Handle to Copy Formulas in Excel

Use fill handle to copy formula in Excel

One use for the fill handle in Excel is to copy a formula down a column or across a row in a worksheet.

Instructions in this article apply to Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.

You usually drag the fill handle to copy the formula to adjacent cells, but there are times you can double-click the mouse to accomplish this task.

This method only works when:

  1. there are no gaps in the data, such as blank rows or columns, and
  2. the formula uses cell references to the location of the data rather than entering the data into the formula.
of 04

Example: Copy Formulas Down With the Fill Handle in Excel

Fill handle filling cells in Excel column

In this example, we will copy a formula in cell F1 to cells F2:F6 by double-clicking the fill handle.

First, however, we will use the fill handle to add the data for the formula to two columns in a worksheet.

Adding data with the fill handle is done by dragging the fill handle rather than double-clicking it. 

of 04

Adding the Data

Data deleted in D7 and E7
  1. Enter the number 1 in cell D1 of the worksheet.
  2. Press the Enter key on the keyboard.
  3. Type the number 3 in cell D2 of the worksheet.
  4. Press the Enter key on the keyboard.
  5. Highlight cells D1 and D2.
  6. Place the mouse pointer over the fill handle (the small black dot in the bottom right corner of cell D2).
  7. The mouse pointer will change to a small black plus sign ( + ) when you have it over the fill handle.
  8. When the mouse pointer changes to the plus sign, press and hold down the mouse button.
  9. Drag the fill handle down to cell D8 and release it.
  10. Cells D1 to D8 should now contain the alternate numbers 1 to 15.
  11. Type the number 2 in cell E1 of the worksheet.
  12. Press the Enter key on the keyboard.
  13. Type the number 4 in cell E2 of the worksheet.
  14. Press the Enter key on the keyboard.
  15. Repeat steps 5 to 9 above to add alternate numbers 2 to 16 to cells E1 to E8.
  16. Highlight cells D7 and E7.
  17. Press the Delete key on the keyboard to delete the data in row 7. This will result in a gap in our data that will stop the formula from being copied to cell F8.
of 04

Entering the Formula

Formula = D1 + E1 entered in F1
  1. Select cell F1 to make it the active cell, which is where we will enter the formula.
  2. Type the formula: = D1 + E1 and press the ENTER key on the keyboard.
  3. Click on cell F1 again to make it the active cell.
of 04

Copying the Formula With the Fill Handle

Formula copied down column F
  1. Place the mouse pointer over the fill handle in the bottom right corner of cell F1.
  2. When the mouse pointer changes to the small black plus sign ( + ), double click on the fill handle.
  3. The formula in cell F1 should be copied to cells F2:F6.
  4. The formula is not copied to cell F8 because of the gap in our data in row 7.
  5. If you select cells E2 to E6, you should see the formulas in those cells in the formula bar above the worksheet.
  6. The cell references in each instance of the formula should change to match the row where the formula is located.