1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Fill Down with the Fill Handle in Excel

By

Double Click the Fill Handle to Copy Formulas
Fill Down with the Fill Handle in Excel

Fill Down with the Fill Handle in Excel

© Ted French

Fill Down with the Fill Handle 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.

Normally we drag the fill handle to copy the formula to adjacent cells but there are times we can simply double click with the mouse to accomplish this task.

This method only works however, when

  1. There are no gaps in the data - such as blank rows or columns, and

  2. the formula is created using the cell references to the location of the data rather than entering the data itself into the formula.

Example: Copy Formulas Down with the Fill Handle in Excel

In this example we will copy a formula in cell F1 to cells F2 : F6 by double clicking on 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 on it.

For help with this tutorial see the image above.

Adding the Data

  1. Type 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. Drag select cells D1 and D2 to highlight them.

  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, click 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. Drag select cells D7 and E7 to highlight them.

  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.

Entering the Formula

  1. Click on cell F1 to make it the active cell - this 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.

Copying the Formula with the Fill Handle

  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 click on 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 the formula is located in.

Related Tuorials

©2014 About.com. All rights reserved.