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.
- 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.
- Firstly, select the E5 cell.
- Then, write down the following formula in the E5 cell.
- After that, press ENTER.
- 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.
- 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.
- 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.
- Firstly, we will open the Developer tab.
- Then, we will select the Visual Basic command.
- Here, the Visual Basic window will open.
- After that, from the Insert option, we will choose the new Module to write a VBA code.
- 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
- Secondly, we call our active worksheet as
- Next, we must copy the desired range from the worksheet that is currently active as
- Then, we will paste the range of formulas to the adjacent cell as
- Finally, we will end the macro as
- Finally, this given image will show you the adjacent F column where the E column’s formula has been copied.
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.