How to Repeat Formula for Each Row in Excel (7 Easy Ways)

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.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


7 Easy Ways to Repeat Formula for Each Row in Excel

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.

excel repeat formula for each row

  • Now, click and drag the cursor to the end of the column.

excel repeat formula for each row

As you can see, this will automatically repeat the formula for each row in the Excel spreadsheet.

excel repeat formula for each row

Read More: How to Autofill in Excel with Repeated Sequential Numbers


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.

excel repeat formula for each row

  • At this instant, simply double-click on the icon.

excel repeat formula for each row

This, too, will repeat the formula for each row for the whole column in the Excel spreadsheet.

excel repeat formula for each row


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 advantage and disadvantage 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.

excel repeat formula for each row

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

excel repeat formula for each row

The formula will now automatically repeat for each row.

excel repeat formula for each row


Similar Readings


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.

excel repeat formula for each row

  • Then select the range you want to repeat the formula.

excel repeat formula for each row

  • And press Ctrl+V to paste them in.

excel repeat formula for each row

This will repeat the formula for each row in the Excel spreadsheet too.

excel repeat formula for each row


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

excel repeat formula for each row

  • Next, press Ctrl+Enter on your keyboard.

This way, you can auto repeat a formula at the beginning instead of replicating afterward.

excel repeat formula for each row

Read More: How to Repeat Formula in Excel for Whole Column (5 Easy Ways)


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.

excel repeat formula for each row

As you can see, this Excel method will repeat the formula for each row too.

excel repeat formula for each row


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.

excel repeat formula for each row

As you can see, this can automatically repeat the formula for each row too.

excel repeat formula for each row

Read More: How to Repeat Formula Pattern in Excel (Easiest 8 ways)


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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo