6 Ways to Copy a Formula Across Multiple Rows in Excel

In our everyday life sometimes we need to work with a large dataset where we have to do the same calculation multiple times. We can do this very easily using the Excel sheet. Moreover, we don’t need to do the same calculation repetitively. We can simply write a formula and copy it to multiple cells to get the result at once. In this article, we will discuss Six methods to copy a formula across multiple rows in Excel. We will utilize different Excel built-in features like Fill Handle, Copy & Paste, Keyboard Shortcut, etc. We will also briefly discuss how we can use the VBA Macro to copy a formula across multiple rows in Excel. You can take an overview of the intention of this article from the snapshot given below.

📁 Download Excel File

Download the practice file from here.

Learn to Copy a Formula Across Multiple Rows in Excel with These 6 Methods

In this article, we will learn 6 simple ways to copy a formula across multiple rows in Excel. Let’s assume that we possess the Product Order Sheet of a particular company. We know the Product, Order & Unit Cost. But we are trying to calculate the Total Cost of all the products. So we will first write a formula for the product Radio and then will copy that formula across multiple rows to calculate the Total Cost of all products. The sample dataset for our products is as follows.

In this section, we will discuss how we can use the Fill Handle tool to copy a formula across multiple rows in Excel. This method is very useful and saves a lot of time. If our dataset consists of repetitive types of calculation we can use this tool to great advantage. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we write the formula in the formula box of cell E6.

• Now we take the cursor to the corner of cell E6.
• After that, we will see a Plus(+) like symbol.
• Now we drag it down.

• Finally, we get our Total Cost in column E.

📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging

In this section, we will see 3 different ways to use the Copy & Paste tool to copy a formula across multiple rows in Excel. Copy & Paste tool is a great tool to copy a value from a cell and paste it into some other cell. If our data is placed in a different location of an Excel file we can simply Copy the formula from one location and Paste it to the location where necessary.

In this particular section, we will use the Copy & Paste tool from Ribbon. So the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the cell E6 formula that we want to Copy.
• Following that we select the Copy dropdown option from the Ribbon.
• After that, we select the Copy option.

• Now we select the cell range where we want to Paste it.
• After that, we go to the Paste option.

• Finally, we get our Total Cost in column E.

In this section, we will apply the Keyboard Shortcut to copy a formula across multiple rows in Excel. Therefore the steps are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select cell E6 and press the Ctrl+C keys.

• Following that we select the cell range where we want to Paste the formula.
• Then we press the Ctrl+V keys together.

• Finally, we get the necessary Total Cost in column E.

Here we will discuss how we can use the Paste Special tool to copy a formula across multiple rows in Excel. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the cell E6 formula and Copy it.

• Following that we select the cell range where we need to Paste it.
• After that, we give a Right Click to our mouse.
• Now we will see some options from where we select the Paste Special option.

• We will find a Dialogue Box.
• Now we press the OK button.

• Finally, we get our necessary Total Cost in column E.

📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel

In this section, we will learn how to copy a formula across multiple rows using Absolute Cell Reference. Sometimes we require to calculate a value where some parameter value is the same for all calculations. So by using the Absolute Cell Reference, we can lock that value and use that in our formula. So the steps for using such a procedure are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to cell F6 and write this formula in the formula box.

• Here we use Absolute Cell Reference for cell C13 by writing $C$13.

• Following that we use the Fill Handle tool to drag the formula.

• Finally we the Present Total Cost in column F.

📕 Read More: Copy Formula and Change Only One Cell Reference in Excel

In this section, we will discuss how to Copy & Paste a formula across multiple rows without formatting. This method is useful if we only want to Copy the formula into some other cell but don’t really want to keep the formatting. So we can use our own preferable formatting for that cell. The steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell E6 formula box and write a formula.
• We use a blue color fill as Formatting.

• Following that we use the Fill Handle tool to drag the formula.

• After dragging we will find a Dropdown box at the bottom right corner.
• Then we select the Fill Without Formatting option.

• Finally, we get the Total Cost but without the Formatting after the first cell in column E.

Here we will take the advantage of the Keyboard Shortcut to copy a formula across multiple rows in Excel. This method is very useful if we don’t have a mouse available or is faulty. Then we can use only the Keyboard buttons to do our job. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the cell D6 formula box and write a formula.

• After that, we go to cell E6 and press these buttons together.

• We will see that the formula is applied to cell E6 also.
• Now we select the whole data range and type these buttons together.

• Finally, we will see the result of Demand in both columns D & E.

In our final method here we will learn how to apply the VBA Macro to copy a formula across multiple rows in Excel. VBA is a powerful tool where we can apply coding beforehand to do spreadsheet calculations. We can save it as a Macro command and later can use it by only running the command. As a result, we can save a lot of time. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we go to the Developer option.
• After that, we select the Visual Basic option.

• Then we go to the Insert option.
• After that, we select the Module option.

• Now we write this script in the Module box.

Code

Sub copy_formula_across_rows()
Range("D6:D11").Copy
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

• Now we close the Module.

• After that, we go to the Macros option.

• We select the Macro name.
• Then we Run the file.

• Finally, we get the Demand value in column E.

📕 Read More: 9 Examples to Copy Formula from Above Cell Using VBA in Excel

How to Copy Excel Sheet with Formulas to Another Workbook

In this section, we will learn to Copy an Excel sheet with formulas to another workbook. If we want to copy the formulas from one spreadsheet to another this method will be useful. It will save time and energy. So the steps for using this method are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• First, we select the Excel sheet and give a Right Click.
• After that, we select the Move or Copy option.

• Following that we select the Workbook that we want to Copy it.
• We select the sheet before it.
• Then we select the Create a copy option.
• Finally, we press the OK button.

• Now we can see a copy of the spreadsheet below.

How to Copy Formulas of Entire Column from One Sheet to Another in Excel

Here in this section, we will learn to copy formulas of the entire column from one sheet to another in Excel. So the steps for using this procedure are as follows.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly we select the cell range from where we want to Copy the formula.
• After that, we go to the Copy Dropdown option.
• Then we select the Copy option.

• Now we go to the sheet where we want to Paste the formula.
• Firstly we select cell E6.
• After that, we select the Paste option.
• Then we select Formulas from the Paste Dropdown options.

• Finally, we will see the result of the Total Cost in column E.

📄 Important Notes

🖊️  We have to save the Excel file as a Macro Enabled file when we use VBA.

🖊️  When we use Keyboard Shortcuts we should be careful about the case sensitivity.

📝 Takeaways from This Article

📌  We can use the Fill Handle tool to copy a formula across multiple rows in Excel.

📌  We can also apply the Copy & Paste feature to copy a formula across multiple rows in Excel.

📌  We can copy a formula across multiple rows in Excel while keeping the Cell Reference.

📌  Also we can copy a formula across multiple rows in Excel without the Formatting.

📌  We can utilize the Keyboard Shortcut to do the same thing.

📌  Apart from these methods we can use the VBA Macro to copy a formula across multiple rows in Excel.

Conclusion

In this article, we have learned 6 different ways to copy a formula across multiple rows in Excel. We can use various kinds of features like the Fill Handle tool, Copy & Paste, Keyboard Shortcut, VBA, etc to copy a formula across multiple rows in Excel. We can do them by keeping a certain cell reference unchanged or without copying the Cell Formatting. Users can use any of the methods they like or feel comfortable with. If you have any queries regarding this particular article you can comment in the comment box. If you want to learn about more Excel-related problems and solutions you can visit our website www.Excelden.com.

Related Articles

• 3 Ways to Copy Formula with Relative Reference Using Excel VBA
• How to Copy Formula Down in Excel Without Incrementing
• 3 Quick Ways to Copy Formula and Paste as Text in Excel