The sample dataset contains some items and the quantity of those items. The item’s quantity increased 5 times in every month. Instead of retyping the formula for each cell individually, we want to copy the formula across multiple rows.
Method 1 – Fill the Copied Formula Right across Multiple Rows
STEPS:
- Click on the cell where the formula is located. Use the following formula to increase the quantity 5 times in every month.
=C5+5
- Drag the Fill handle right to the range E5:F5. The formula will be visible in the formula bar.
- This will copy the formula to the selected cells.
- To copy the formula in multiple rows, drag the Fill Handle down over the range.
- The formula is copied in multiple rows.
Read More: How to Copy Exact Formula in Excel
Method 2 – Copy & Paste a Formula across Multiple Rows in Excel
STEPS:
- Select the cell that contains the formula (D5).
- Go to the Home tab from the ribbon.
- Click on Copy under the Copy drop-down menu bar in the Clipboard category.
- Select the cells that we want to copy the formula.
- Click on Paste.
- The formula will be copied to the other cells.
To copy the formula into multiple rows select the row range D5:F5.
- From the ribbon, select the Home tab.
- Select Copy from the Copy drop-down menu bar under the Clipboard group.
- Select multiple rows where we want the formula to be copied.
- Click on Paste.
- This will copy the formula in multiple rows.
Method 3 – Keyboard Shortcuts to Duplicate a Formula
STEPS:
- To copy the formulated cell press Ctrl + C.
- Paste the formula to other cells by pressing Ctrl + V.
- 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.
- Select multiple rows to copy the formula.
- Paste it by pressing the keyboard shortcut Ctrl + V.
Method 4 – Paste Special Feature to Copy a Formula
STEPS:
- To copy the cell with the formula, right-click on your mouse and select Copy.
- Select multiple rows to copy the formula.
- Right-click on your mouse and select Paste Special.
- This will open the Paste Special dialog box.
- Select All under the Paste option menu.
- Select None under the Operation option menu.
- Click on the OK button.
- The formula is copied in multiple rows.
Read More: How to Copy SUM Formula in Excel
Method 5 – Copy a Formula across Multiple Rows without Changing the Cell Reference
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.
The sample dataset contains some items and the price of those items. We want to calculate the price value with VAT and also the price increase every month. To do this, we need to copy the formula across multiple rows.
STEPS:
- We want to calculate the price with a VAT of 7%. Enter the formula below in cell D5.
=C6*($F$4+1)
- To copy the formula, press the keyboard shortcut Ctrl + C.
- Select the rows where you want to paste the copied formula with absolute references, which is cell F4.
- Select cell range E6:F6 and D8:F8.
- Paste the formula by pressing Ctrl + V.
- Copy the formula in every row.
- Select the whole range of those rows and paste the formula by using the Ctrl + V key on your keyboard.
Read More: How to Copy Formula Down with Shortcut in Excel
Method 6 – Copy the Formula in Excel without Formatting
STEPS:
- Select the cell with the formula, The formula will appear in the formula bar. The formula is:
=C5+5
- Copy the cell and paste it into other cells. This will copy the formula with formatting.
- To remove the formatting, click on the option shown in the picture.
- Go to Formatting > Clear.
- Alternatively press F on your keyboard to select Formatting and select Clear.
- The formula is now copied to multiple rows without formatting.
Related Content: Creating and Copying Formula Using Relative References in Excel
Method 7 – Keyboard Shortcut to Copy a Formula over Multiple Rows
STEPS:
- Select the row with the formula, here it is cell range D5:F5.
- Press the keyboard shortcut Ctrl + R. This will copy the formula to the cells on the right in the dataset.
- Press the keyboard shortcut Ctrl + D.
- 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 updated.
Download Practice Workbook
You can download the workbook and practice with them.
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!
