Copy a formula by dragging the fill handle in Excel 2016 for Mac

You can quickly copy formulas into adjacent cells by using the fill handle.

Fill handle

When you drag the handle to other cells, Excel will show different results for each cell.

Copy a formula by dragging the fill handle

  1. Select the cell that has the formula you want to fill into adjacent cells.

  2. Rest your cursor in the lower-right corner so that it turns into a plus sign (+), like this:

    Cursor resting on fill handle

  3. Drag the fill handle down, up, or across the cells that you want to fill. In this example, we are dragging the fill handle down:

    Cursor dragging fill handle down

  4. When you let go, the formula gets automatically filled to the other cells:

    Values showing for cells that have been filled

  5. To change how you want to fill cells, click the Auto Fill Options button Auto Fill Options button that appears after you finish dragging, and pick the option that want.

For more information about copying formulas, see Copy and paste a formula to another cell or worksheet.

Tips:  You can also press CONTROL + D to fill the formula down in a column. First select the cell that has the formula you want to fill, then select the cells underneath it, and then press CONTROL + D.

You can also press CONTROL + R to fill the formula to the right in a row. First select the cell that has the formula you want to fill, then select the cells to the right of it, and then press CONTROL + R.

When filling doesn't work

Sometimes filling cells works great, and other times it doesn't work as you expect. That's why it's important to check the cell references of the first cell.

Cell references are simply pointers to other cells. For example let's say the first cell has this formula:


=SUM(A1,B1)


A1 and B1 are relative references. This just means that when you fill the formula down, the references will change incrementally from A1, B1 to A2, B2 and so on, like this:

=SUM(A1,B1)

=SUM(A2,B2)

=SUM(A3,B3)

Other times you don't want the references to other cells to change. For example, let's say that you wanted the first reference, A1, to stay fixed and B1 to change as you drag the fill handle. If this is the case, you'd type dollar signs in the first reference like this: =SUM($A$1,B1). The dollar signs force Excel to keep pointing to A1 while you fill other cells. This is what it would look like:

=SUM($A$1,B1)

=SUM($A$1,B2)

=SUM($A$3,B3)

References with dollar signs ($) are called absolute references. When you fill down, the reference to A1 stays fixed but Excel changes the reference of B1, to B2 and B3.

Having trouble seeing the file handle?

If you don’t see the fill handle, it might be hidden. To show it again:

  1. On the Excel menu, click Preferences.

  2. Click Edit.

  3. Under Edit Options, check the Allow fill handle and cell drag-and-drop box.

If automatic workbook calculation isn’t turned on, formulas won’t recalculate when you fill cells. Here’s how to turn it on if needed:

  1. On the Excel menu, click Preferences.

  2. Click Calculation.

  3. Under Calculation Options, make sure that Automatic is selected.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×