How to Copy Formula in Excel to Other Cells (2 Easy Ways)

While you are working with Excel, you will have to copy formulas frequently from one cell to another cell. This article will show you how to copy formula in Excel to other cells.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


2 Easy Ways to Copy Formula in Excel to Other Cells

In this article, we will demonstrate to you how to copy formulas in Excel to other cells by using the Fill Handle tool and applying VBA to copy formulas with changing cell references in Excel.


Method 1: Using Fill Handle Tool to Copy Formula in Entire Column

In this article, we will demonstrate to you how to copy formula in Excel to other cells by using the Fill Handle tool with some easy steps below.

Step 1:

  • At the start of this section, we have created our sample data set where we will apply the product formula to determine the total price of all the products.

Easy Ways to Copy Formula in Excel to Other Cells

Step 2: 

  • Firstly, select the E5 cell.
  • Then, write down the following formula in the E5 cell.
=C5*D5
  • After that, press ENTER.

Using Fill Handle Tool to Copy Formula in Entire Column

Step 3: 

  • Consequently, you will see the total price of all the watches in the  E5 cell.
  • To copy this formula to other cells of column E, just click on the Fill Handle, hold your mouse, and drag down until you have reached cell E10 in our data set. Or you can use this technique. Click on cell E5. While clicking cell B4, press Shift and use the navigation arrow (⇓) to reach cell E10. Cells are selected, now click CTRL+D to auto-fill all cells (you have selected) with the formula in cell E5.

Step 4:

  • Finally, you will see all the total prices for all the products here in the below image after copying the formula.


Method 2: Applying VBA to Copy Formula with Changing Cell Reference in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to copy formula in Excel with changing cell reference.

Step 1:

  • Here, we will apply the product formula in the E column.
  • Then, we want to copy this formula in the adjacent F column with changing cell reference.

Step 2:

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Applying VBA to Copy Formula with Changing Cell Reference in Excel

Step 3:

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA code.

Step 4:

  • Now, paste the following VBA code into the Module
  • To run the program, click the “Run” button or press F5.
Sub Copy_Formula_with_Changing_Cell_References()
'Activate the worksheet
Worksheets("Sheet3").Activate
'Copy the range
Range("E5:E10").Copy
'Paste the adjacent cell
Range("F5").PasteSpecial Paste:=xlPasteFormulas
End Sub

VBA Code Breakdown

  • Firstly, we specify a name for the subject as
Sub Copy_Formula_with_Changing_Cell_References()
  • Secondly, we call our active worksheet as
Worksheets("Sheet3").Activate
  • Next, we must copy the desired range from the worksheet that is currently active as
Range("E5:E10").Copy
  • Then, we will paste the range of formulas to the adjacent cell as
Range("F5").PasteSpecial Paste:=xlPasteFormulas
  • Finally, we will end the  macro as
End Sub

Step 5:

  • Finally, this given image will show you the adjacent F column where the E column’s formula has been copied.


Conclusion

In this article, we’ve covered 2 handy ways to copy formula in Excel to other cells. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo