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.
How to Copy a Formula Across Multiple Rows in Excel: 5 Suitable Ways
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. 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.
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 a simple formula.
=C5+5
- Next, drag the Fill handle rightwards to the range E5:F5. The formula will show in the formula bar.
- This will copy the formula to drag cells.
- After that, to copy the formula in multiple rows, drag the Fill Handle down over the range.
- And, finally, the formula is copied in multiple rows.
Read More: How to Copy Exact Formula in Excel
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.
- And this will copy the formula in multiple rows.
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.
- 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.
- 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: How to Copy SUM Formula in Excel
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.
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.
- 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 Formula Down with Shortcut in Excel
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: Creating and Copying Formula Using Relative References in Excel
5. Keyboard Shortcut to Copy a Formula over Multiple Rows
Keyboard shortcuts can make work faster, and 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, 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 Formula in Excel to Change One Cell Reference Only
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.
Download Practice Workbook
You can download the workbook and practice with them.
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.
Related Articles
- How to Copy a Formula in Excel Without Changing Cell References
- How to Copy Formula to Entire Column in Excel
- How to Copy a Formula in Excel with Changing Cell References
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
