How to Copy a Formula across Multiple Rows in Excel (5 Ways)

In Microsoft Excel, we may occasionally have to repeat the same formula to a whole column or row. If we enter the formula in each cell one by one, it will take a long time. There are a few methods for swiftly applying the same formula to multiple rows in excel. In this article, we will demonstrate the methods to copy a formula across multiple rows in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


5 Ways to Copy a Formula across Multiple Rows in Excel

Copying a formula is one of the useful works in excel to save time and do the work faster. Excel has several ways to copy and paste formulas in multiple rows. We will look at numerous ways to copy a formula across multiple rows in Excel.

To do this, we are going to use the dataset below, which contains some items and the quantity of those items. The item’s quantity increased 5 times in every month. For this, we are going to use a simple formula. And instead of retyping the formula for each cell individually, we want to copy the formula across multiple rows. So, let’s go through the methods to copy the formula across multiple rows in Excel.5 Ways to Copy a Formula Across Multiple Rows in Excel


1. Fill the Copied Formula Right across Multiple Rows

To copy a formula across multiple rows, we can use the Fill Handle. To use the feature of excel, we need to follow some steps down.

STEPS:

  • In the beginning, click on the cell where the formula is located. To increase the quantity 5 times in every month. We are using the simple formula down.
=C5+5

5 Ways to Copy a Formula Across Multiple Rows in Excel

  • Next, drag the Fill handle rightwards to the range E5:F5. The formula will show in the formula bar.

Fill the Copied Formula Right Across Multiple Rows

  • This will copy the formula to drag cells.

Fill the Copied Formula Right Across Multiple Rows

  • After that, to copy the formula in multiple rows, drag the Fill Handle down over the range.

Fill the Copied Formula Right Across Multiple Rows

  • And, finally, the formula is copied in multiple rows.

Fill the Copied Formula Right Across Multiple Rows

Read More: How to Copy Exact Formula in Excel (13 Methods)


2. Copy & Paste Options for Copying a Formula across Multiple Rows in Excel

Copy and paste are some of the useful tools to save time. We can copy and paste the data or formulas in various ways in Excel.

2.1. Using Ribbon to Copy a Formula in Excel

We can use the ribbon to copy a formula across multiple rows. To use the ribbon, we have to demonstrate the steps down.

STEPS:

  • Firstly, select the formulated cell. So, we select cell D5.
  • Secondly, go to the Home tab from the ribbon.
  • After that, click on Copy under the Copy drop-down menu bar in the Clipboard category.

  • Then, select the cells that we want to copy the formula.
  • Next, click on Paste.

  • By clicking Paste the formula will be copied on the other cells.

  • Now, to copy the formula into multiple rows, select the row range D5:F5.
  • From the ribbon, select the Home tab.
  • Further, select Copy from the Copy drop-down menu bar under the Clipboard group.

  • Select multiple rows where we want the formula to be copied.
  • Here, click on Paste.

Copy & Paste Options for Copying a Formula Across Multiple Rows in Excel

  • And this will copy the formula in multiple rows.

Copy & Paste Options for Copying a Formula Across Multiple Rows in Excel


2.2. Keyboard Shortcuts to Duplicate a Formula

We can use a keyboard shortcut to copy and paste a formula across multiple rows. For this, we need to follow some simple steps.

STEPS:

  • Likewise, the other methods copy the cell which has the formula. The formula will show in the formula bar. To copy the formulated cell press Ctrl + C.

Keyboard Shortcuts to Duplicate a Formula

  • Then, paste the formula to other cells by pressing Ctrl + V.

  • Now, to copy the formula in multiple rows, select the row that is copied before. And copy this row using the previous keyboard shortcut Ctrl + C.

  • Further, select multiple rows to copy the formula.

  • Finally, paste it by pressing the keyboard shortcut Ctrl + V.
  • And, that’s it! This will copy the formula in multiple rows.


2.3. Paste Special Feature to Copy a Formula

To copy a formula across multiple rows we can use the Paste Special feature. To use this feature follow the procedure down.

STEPS:

  • Firstly, to copy the cell with the formula, right-click on your mouse and select Copy.

  • After that, select multiple rows to copy the formula. So we select the cell range D5:F8.
  • Next, again, right-click on your mouse and select Paste Special.

Paste Special Feature to Copy a Formula

  • This will open the Paste Special dialog box.
  • Select All under the Paste option menu. And, select None under the Operation option menu.
  • Then, click on the OK button.

  • Finally, the formula is copied in multiple rows.

Read More: Shortcut to Copy Formula Down in Excel (7 Ways)


3. Copy a Formula across Multiple Rows without Changing the Cell Reference

While generating formulas, cell references are a vital part. They permit updating data without rewriting formulas in the worksheet. When a formula is duplicated to another cell, relative cells can be changed. On the other hand, absolute references do not change regardless of where they are duplicated.

To copy a formula across multiple rows we are using the formula dataset. The dataset contains some items and the price of those items. We want to calculate the price value with vat and also the price will increase every month. To do this, we need to copy the formula across multiple rows. Let’s look at the procedures to copy the formula.

Copy a Formula Across Multiple Rows without Changing the Cell Reference

STEPS:

  • In the first place, we want to calculate the price with a vat of 7%. We are using the formula below in cell D5.
=C6*($F$4+1)

  • Then, to copy the formula, press the keyboard shortcut Ctrl + C.
  • Further, select the rows where you want to paste the copied formula with absolute references, which is cell F4. so, we select cell range E6:F6 and D8:F8.

Copy a Formula Across Multiple Rows without Changing the Cell Reference

  • Furthermore, paste the formula by pressing Ctrl + V.

  • Now, if you want, copy the formula in every row. Then, just select the whole range of those rows after that, paste the formula by using the Ctrl + V key on your keyboard.

Read More: How to Copy a Formula in Excel with Changing Cell References


Similar Readings:


4. Copy the Formula in Excel without Formatting

Formatting a cell or a range of cells makes the dataset more understandable. Suppose, we use a color format to understand that the cell has the main formula. But we want to copy the formula without formatting. Let’s follow the procedure below.

STEPS:

  • First, select the cell with the formula, So, we select cell D5. By selecting the cell, the formula will appear in the formula bar. The formula is:
=C5+5

  • Second, copy the cell and paste it on other cells. This will copy the formula with formatting.

  • To remove the formatting, you need to click on the option shown in the picture.
  • Then, go to Formatting > Clear.

  • Instead of doing this, while pasting the formula to other rows. There is an option, named Formulas or press F on your keyboard to avoid the formatting.

  • And, there you go! The formula is now copied to multiple rows without formatting.

Related Content: How to Copy Formula in Excel (6 Quick Methods)


5. Keyboard Shortcut to Copy a Formula over Multiple Rows

Keyboard shortcuts can make work faster, more efficient, saving time and increasing productivity. To use the keyboard shortcuts to copy a formula over multiple rows, we just need to follow the steps below.

STEPS:

  • Firstly, select the row with the formula, So we select cell range D5:F5.

  • Secondly, press the keyboard shortcut Ctrl + R. This will copy the formula rightwards to the dataset.

  • After that, to copy multiple rows at a time. Select the whole data.
  • Then, simply press the keyboard shortcut Ctrl + D.

  • And, that’s it! The formula will copy to multiple rows in your data list.

Related Content: How to Copy a Formula Down the Column in Excel(7 Methods)


Excel Not Copying Formulas

If your Excel file is set to manual recalculation, excel may not copy the formula. To solve this problem, select any cell in the spreadsheet and press the F9 key. By pressing the keyboard key, every formula in the excel file is instantly updated.

Excel Not Copying Formulas


Conclusion

The above methods will assist you to copy a formula across multiple rows in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo