Using the Fill Handle to Copy Formulas in Excel

Using the Fill Handle and Formatting

To complete the worksheet, we need to copy the Deduction formula to cells C9 to C11 and the Net Salary formula to cells D9 to D11.

A quick way of copying formulas in Excel is to use the Fill Handle.

In addition to copying formulas and data, the fill handle will also copy formatting. This means that it can mess up existing formats in cells that formulas are being copied to.

A common example of this is overwriting shading that has been applied to alternate rows in the worksheet.

To prevent the Fill Handle from overwriting existing formatting, use Fill without Formatting found in the Auto Fill Options drop down menu.

For this tutorial:

Note: For help with these instructions, see the image above.

1. Click on cell C8 to make it the active cell.


2. Place the mouse pointer over the black square in the bottom right corner. The pointer will change to a plus sign " + ".


3. Click the left mouse button and drag the fill handle down to cell C11.


4. Release the mouse button. Cells C9 to C11 should contain the results of the Deduction formula and the background shading in cells EC9 and C11 should be gone.


5. When you release the fill handle the Auto Fill Options drop down menu should also appear below cell E12.


6. Click on the down arrow in the menu to open it.


7. Click on the third option Fill without Formatting to select it.


8. The background shading in cells C9 and C11 should reappear.

To speed up copying formulas, the fill handle can be used to copy formulas in more than one column at a time. To fill in the formulas in cells D8 to F8:

1. Drag select cells D8 to F8 to highlight them.


2. Repeat steps 2 to 8 above to copy the Net Salary formula without removing the cell shading.