How to Use Fill Handle to Copy Formula in Excel (2 Examples)

Fill Handle is a very effective tool to copy formulas in Excel. By knowing how to use the Fill Handle to copy a formula in excel, we can perform thousands of calculations, based on one equation, within a very short time by using this tool in Excel. In this article, we’ll discuss 2 examples of how to use the Fill Handle to copy formulas in Excel.

To work on how to use Fill Handle to copy formulas in Excel, we have made a dataset of some of the companies in California, U.S. which includes “Selling Product”, “Number of Employee”, “Early Revenue(M)”, “Tax Fee(M)”, and “Salary Cost(M)”. In the case of the following dataset, we will use the Fill Handle tool both vertically and horizontally.

1. Copying a Formula Vertically by Dragging Fill Handle in Excel

We can use Fill Handle to copy formulas in Excel. Also, we can use this to copy data, create sequences, duplicate things, delete things, etc. But the most awesome one is to copy formulas both horizontally and vertically. To copy formulas vertically, we can do the task by doing the following steps.

Step 01: Select the Cell

To copy a formula, we first need to use that formula in a cell. We have used the SUM function to calculate the sum of D5:F5 cells in the G5 cell.

Now to copy that SUM function to cells from G6 to G14, we first need to select the reference formula cell i.e G5.

Step 02: Place Cursor on the Cell

We then need to place the cursor in the lower right bottom corner of the selected cell just like the image shown below.

Step 03: Drag Fill Handle Down Vertically

After placing the cursor on the lower right bottom corner of the reference cell, we then need to glide the cursor vertically by holding the left key of the mouse.

The arrow in the picture shows how we should drag down the cursor over the cells.

We shall then check the cells. It will show that the SUM function is placed there according to their reference values just as we did for the G5 cell. In the below picture, the G6 cell is the sum of the D6 cell to the F6 cell.

Step 04: Use AutoFill Option

We can select the various AutoFill options by clicking on the AutoFill bar and then we can select one option from the four options displayed.

2. Dragging Fill Handle to Copy a Formula Horizontally

To copy formulas horizontally we can do the following steps.

Step 01: Select the Cell

To drag a formula horizontally with vertical reference, we first need to use that formula in a cell. We have used the SUM function to calculate the sum from D5 to D14 cells in the G15 cell.

Now to copy that SUM function to cells E15 and F15, we first need to select the reference formula cell i.e D15 cell.

Step 02: Place Cursor on the Cell

We then need to place the cursor on the lower right bottom corner of the selected D15 cell just like the image shown below.

Step 03: Drag Fill Handle Down Horizontally

After placing the cursor on the lower right bottom corner of the reference  D15 cell, we then need to glide the cursor horizontally by holding the left key of the mouse.

The arrow in the picture shows how we should drag the cursor over the cells in the right side direction.

We shall then check the cells. It will show that SUM functions are placed there according to their reference values just as we did for the D15 cell. In the above picture, the E15 cell is the sum of the E5 cell to the E14 cell.

Step 04: Use AutoFill Option

We can select the various AutoFill options by clicking on the AutoFill bar and then we can select one option from the three options displayed.

Read More: How to Enable Drag Formula in Excel

How to Use Keyboard Shortcut to Copy Formula in Excel

Luckily there is an alternative way to copy the formula both vertically and horizontally. We can use  to copy and drag formulas with keyboard in Excel. It will be useful when you need to save time.

1. Horizontal Copy of Formula

We can use keyboard shortcuts. For a horizontal copy of the formula, we first need to place the formula in the reference cell. Then we should place the cursor on the next horizontally rightwards cell.

Then, we should press the CTRL+R button to place the same function.

Here the output of the E15 cell is the sum of the E4 cell to the E14 cell just like the reference D15 cell.

Read More: How to Drag Cells in Excel Using Keyboard

2. Vertical Copy of Formula

For a vertical copy of the formula, we first need to place the formula in the reference cell. Then we should place the cursor on the next vertically downwards cell.

Then we should press the CTRL + D button to place the same function.

Here the output of the G6 cell is the sum from the D6 cell to the F6 cell just like the reference G5 cell

Read More: [Solved]: Fill Handle Not Working in Excel

Things to Remember

• We need to click exactly on the right bottom corner of the reference cell to drag down or drag right. Clicking on the center of the cell will not act as a Fill Handle.
• The keyboard shortcut is not included in the Fill Handle. It is mainly a shortcut that we can do for casual use.

Download Practice Workbook

Conclusion

“Fill Handle” is a very effective system to copy formulas in thousands of cells within a very short time. We can use it both vertically and horizontally for every formula used in Excel.

Related Articles

• Excel Fill Down to Next Value
• How to Fill Formula Down to Specific Row in Excel
• How to Drag Formula and Ignore Hidden Cells in Excel

<< Go Back to Fill Handle in Excel | Learn Excel