Copying Formulas and Formatting with the Fill Handle
To complete the worksheet, we need to copy the Total Salary, Deduction, and Net Salary formulas to rows 9 to 11.
A quick way of copying formulas in Excel is to use the Fill Handle. The fill handle is located in the bottom right corner of the active cell in an Excel worksheet.
In addition to copying formulas and data, the fill handle will also copy formatting. This means that if you are not careful it can mess up the formatting in the destination cells.
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 the Fill without Formatting option found in the Auto Fill Options drop down menu.
Tutorial Steps
For help on these steps, refer to the image above.
- Click on cell C8 to make it the active cell.
- Place the mouse pointer over the small black square in the bottom right corner of the active cell. The pointer will change to a plus sign " + ".
- Click the left mouse button and drag the fill handle down to cell C11.
- Release the mouse button. Cells C9 to C11 should contain the results of the Deduction formula and the background shading in cells C9 and C11 should be gone.
- When you release the fill handle the Auto Fill Options drop down menu should also appear below cell E12.
- Click on the down arrow in the menu to open it.
- Click on the third option Fill without Formatting to select it.
- The background shading in cells C9 and C11 should reappear.
Copy Formulas in Multiple Cells with the Fill Handle
The fill handle can be used to copy data from several cells at the same time.
To copy the formulas in cells D8 to F8 to rows 9, 10, and 11, do the following:
- Drag select cells D8 to F8 to highlight them.
- Repeat steps 2 to 7 above to copy the Net Salary formula without removing the cell shading.
If you have followed all the steps in this tutorial your worksheet should now resemble the image seen in step 1 of this tutorial.
Return to Index page
