It is very common to repeat a formula for each row for multiple columns in Excel spreadsheets. And Excel has multiple ways to do that conveniently. Instead of typing or inputting formulas manually each time, following these methods automate the process. In this tutorial, we are going to see how we can repeat a formula for each row in Excel.
How to Repeat Formula for Each Row in Excel: 7 Easy Ways
Excel has a total of seven different methods to repeat a formula for each row. We are going to show each one in its sub-section. For demonstration, we are going to use the following dataset.
Usually, the total cost would be the number of units sold times the cost per unit. But if you want to do that manually every time you have to type in the formula. Instead, we can use one of the following methods.
1. Dragging Fill Handle Icon
The first method we are going to demonstrate here is the dragging of the Fill Handle icon. The main idea is to click and drag the Fill Handle icon after selecting a particular cell to replicate. Once you have inserted a formula in a cell, replicating the cell will actually replicate the formula for cells with their respective or absolute references depending on the formula. The advantage of using this method is you can stop replicating the formula at your desired position if you don’t want it repeated for the whole column.
For a more detailed method, follow these steps.
Steps:
- First of all, select cell E5.
- Then write down the following formula in it.
=C5*D5
- After that, press Enter on your keyboard. Now you will have the formula for the first cell.
- Next, select the cell again and move the mouse cursor to the bottom right end of the cell. Once you have done that, the mouse cursor will change to the one shown in the figure.
- Now, click and drag the cursor to the end of the column.
As you can see, this will automatically repeat the formula for each row in the Excel spreadsheet.
Read More: How to Repeat Cell Values in Excel
2. Double Clicking Fill Handle Icon
Instead of clicking and dragging the Fill Handle all the way down, we can also double-click the icon to repeat the formula for each row in Excel. This will repeat the formula for the whole row. The advantage of using this method over the previous one is, that you can quickly fill out formulas for long columns. You don’t have to manually drag them down- this is advantageous if you have thousands of rows in a column. Then again, the downside is you can’t stop at a particular position with this method, it will always stop at where the previous or later column has ended.
Follow these steps for a detailed guide on the method.
Steps:
- First of all, select cell E5.
- Now write down the following formula in it.
=C5*D5
- Then press Enter.
- Now select the cell again and move your cursor to the bottom right end of the selection. The cursor icon will change to the one shown in the figure.
- At this instant, simply double-click on the icon.
This, too, will repeat the formula for each row for the whole column in the Excel spreadsheet.
Read More: How to Repeat Cell Value X Times in Excel
3. Applying Down Command from Fill Drop-Down
There is a fill feature in the Editing group in the Excel ribbon. You can also use that to repeat a formula for each row in Excel. The advantages and disadvantages of using this method are the same as the first method- you can stop wherever you want, but you have to manually select the range.
Follow these steps for more details.
Steps:
- First, select cell E5.
- Second, write down the following formula.
=C5*D5
- Third, press Enter.
- Then select the range of cells where you want the formula to be repeated. The selection should include the previous cell. Here, we have selected the range E5:E10 for the demonstration.
- Then go to the Home tab of your ribbon.
- After that, click on Fill from the Editing
- Next, select Down from the drop-down list.
The formula will now automatically repeat for each row.
4. Copying and Pasting Formula
You can also repeat a formula for each row if you directly copy a cell and paste it into new cells. The pasted formulas will maintain their relative references and you will get the formula repeated for each row/column, or any cells for that matter.
Follow these steps for a more detailed guide.
Steps:
- First, select cell E5.
- Then write down the following formula in it.
=C5*D5
- After that, press Enter.
- Now select the cell and press Ctrl+C on your keyboard.
- Then select the range you want to repeat the formula.
- And press Ctrl+V to paste them in.
This will repeat the formula for each row in the Excel spreadsheet too.
Read More: How to Repeat Rows in Excel Based on Cell Value
5. Applying Formula for Entire Column
Instead of writing a formula for a column and then copying or repeating it to other row cells, Excel has a feature that lets us enter a formula for the entire column. This will automatically repeat the formula for each row of the selected section.
Follow these steps to see how you can accomplish that.
Steps:
- First, select the whole column where you want to repeat a formula.
- Then write down the following formula.
=C5*D5
- Next, press Ctrl+Enter on your keyboard.
This way, you can auto repeat a formula at the beginning instead of replicating it afterward.
Read More: How to Repeat Formula in Excel for Whole Column
6. Using Keyboard Shortcut
Instead of applying a formula for a whole column, you can also apply it for a single cell and then use the keyboard shortcut to repeat the formula to the cells you desire.
Follow these steps for a detailed guide.
Steps:
- First of all, select cell E5.
- Then write down the following formula in it.
=C5*D5
- After that, press Enter.
- Now select the range where you want the formula to be repeated.
- Finally, press Ctrl+D on your keyboard.
As you can see, this Excel method will repeat the formula for each row too.
7. Utilizing Array Formula
This final method works in Microsoft Excel 365 only. Unfortunately, for the previous versions, this won’t be applicable. But if you have Office 365 installed on your system, this quick method might come in handy.
Follow these steps to see how you can use the array formula to repeat the same formula for each row.
Steps:
- First, select cell E5.
- Then write down the following formula in it.
=C5:C10*D5:D10
- After that, press ‘Ctrl+Shift+Enter’ to automatically fill up the cells with the formula.
As you can see, this can automatically repeat the formula for each row too.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These were all the methods you can use to repeat a formula for each row in Excel. Hopefully, you can repeat your formulas with ease now with the method you prefer. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
Related Articles
- How to Repeat Multiple Rows in Excel
- How to Repeat Formula in Every nth Row in Excel
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
