How to Use Fill Handle to Copy Formula in Excel (2 Useful 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.


Download Practice Workbook


2 Examples to Use Fill Handle to Copy Formula 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.

how to use fill handle to copy formula in excel


1. Copying a Formula Vertically by Dragging Fill Handle

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.

how to use fill handle to copy formula in excel

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.

how to use fill handle to copy formula in excel

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.

how to use fill handle to copy formula in excel

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.

how to use fill handle to copy formula in excel

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. Copying a Formula Horizontally by Dragging Fill Handle

To copy formulas horizontally we can do 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 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 to the lower right bottom corner of the selected D15 cell just like the image shown below.

how to use fill handle to copy formula in excel

Step 03: Drag Fill Handle Down Horizontally

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

how to use fill handle to copy formula in excel

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.

how to use fill handle to copy formula in excel

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 Drag Formula Horizontally with Vertical Reference in Excel


Similar Readings


Using Keyboard Shortcut to Copy Formula

Luckily there is an alternative way to copy the formula both vertically and horizontally. It will be useful if we are accustomed to using keyboard shortcuts.


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.

how to use fill handle to copy formula in excel

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


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: How to Drag Formula in Excel with Keyboard (7 Easy Methods)


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.

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

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo