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.

Copy a Formula Across Multiple Rows in Excel


📁 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.

Sample Dataset of Product Order Sheet

method

1. Using Fill Handle Tool to Copy Formula and Paste Values Across Multiple Rows in Excel

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.

Using a formula in a cell to get the Total Cost

  • 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.

Using the Fill Handle tool to drag

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

Applied formula across multiple rows

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

method

2. Applying Copy & Paste Feature to Copy Formula Across Multiple Rows

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.

Approach

2.1 Utilizing Ribbon Feature

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.

Using the copy option to copy the formula

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

Using the paste option to paste the formula

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

Copying the formula, applied across multiple rows in Excel.

Approach

2.2 Applying Keyboard Shortcut

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.

Input a formula and copy it to paste across multiple rows in Excel.

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

The pasting formula in the remaining cells.

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

Application of the formula across multiple rows in Excel

Approach

2.3 Using Paste Special Option

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.

Cell value to use Paste Special

  • 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.

Using the Paste special to Paste formula across multiple rows

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

input in the Paste Special box

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

Application of paste formula across multiple rows

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

method

3. Keeping Cell Reference Unchanged to Copy Formula Across Multiple Rows

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.

=C6*(D6+$C$13*D6)

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

Using a formula to get the Total cost

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

Using the Fill handle tool to drag the formula

  • Finally we the Present Total Cost in column F.

Application of paste formula across multiple rows

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

method

4. Copying Formula and Paste Exactly Across Multiple Rows Without Formatting

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.

cell value with blue color formatting

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

Use of the fill handle tool to copy the formula across multiple rows

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

Applied formula across multiple rows with formatting

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

Applied formula across multiple rows without formatting

method

5. Implying Keyboard Shortcut to Copy a Formula Across Multiple Rows in Excel

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.

Using a formula to get the Demand

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

Ctrl+R

Applying the keyboard shortcut to copy

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

Ctrl+D

Using the keyboard shortcut to paste

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

Applied formula across multiple rows

method

6. VBA Macro to Copy Formula Across Multiple Cells

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.

Selecting the Visual basic to get the Demand

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

Inserting a module to write a code

  • 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.

Writing a code to get the Demand

  • After that, we go to the Macros option.

Selecting Macros to get the Demand

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

Running Macro file to get the Demand

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

Applied formula across multiple rows

📕 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.

Selecting the Move or copy option to copy

  • 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.

Creating a copy from the dialogue box

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

New Duplicate Excel sheet


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.

Selecting the copy option to copy

  • 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.

Selecting the paste option to paste

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

Applied formula across multiple columns


📄 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

(Visited 21 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo