How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)

Sometimes, we apply a formula in a cell and then repeat the same formula in every nth row in Microsoft Excel. Here n can be any integer like 1,2,3,4,… But repeating the same formula again and again in every nth row can be a hectic and time-consuming task. When you have a large dataset, sometimes it becomes quite impossible to repeat the formula manually. This tutorial will help you to learn how to repeat formula in every nth row in Excel in 2 easy ways. Let’s get started!


Download Practice Workbook

You can download the Excel workbook from here.


2 Easy Ways to Repeat Formula in Every nth Row in Excel

There are 2 easy ways to repeat formula in every nth row in Excel. One is by using Fill Handle and the other one is by combining the MOD function and the ROW function. Now we will see examples of how to solve this problem by these 2 methods.


1. Use of Fill Handle

We can repeat formula in every nth row in Excel using the Fill Handle. This is an easy and effective way to do the job. We have taken a dataset where we have Student ID numbers of 6 students. We will write their Student ID numbers in concatenate form using a formula and repeat the same formula every 2 rows using the Fill Handle.

Suitable Ways to Repeat Formula in Every nth Row in Excel

In order to use this method, follow the steps below.

Steps:

  • First, select cell C5 and type the following formula:
=CONCATENATE(B5,",",B6)
  • Now, press Enter.
  • As a result, you will see an output like the image below where it will display concatenate form of the Student IDs of cells B5 and B6.

Suitable Ways to Repeat Formula in Every nth Row in Excel

  • Second, select cells B5 and B6 as shown below.

Suitable Ways to Repeat Formula in Every nth Row in Excel

  • Third, drag the Fill Handle to the entire column Repeat Formula.
  • As a result, you will see an output like the below one where it will repeat the formula every 2 rows showing the concatenate form of 2 Student IDs.


2. Combining MOD and ROW functions

We can also repeat formula in every nth row in Excel combining the MOD and the ROW functions. This is also an effective way to perform the task. We have taken the previous dataset where we have Student ID numbers of 6 students. We will write their Student ID numbers in concatenate form using a formula and repeat the same formula every 2 rows combining MOD and ROW functions.

Suitable Ways to Repeat Formula in Every nth Row in Excel

In order to use this method, follow the steps below.

Steps:

  • First, select cell C5 and type the following formula:
=IF(MOD(ROW(),2)=1,B5&" ,"&B6,"")
  • Now, press Enter.
  • As a result, you will see an output like the image below where it will display concatenate form of the Student IDs of cells B5 and B6.

Suitable Ways to Repeat Formula in Every nth Row in Excel

  • Second, select cell C5 and drag the Fill Handle to the entire column Repeat Formula.
  • Finally, you will see an output like the below one where it will repeat the formula every 2 rows showing the concatenate form of 2 Student IDs.

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


Similar Readings


Utilize Combination of OFFSET and ROW Functions to Repeat Data from Every nth Row in Excel

Sometimes we have to repeat data from every nth row from another dataset. This operation can be done by combining the OFFSET function and the ROW function. We can do this task in 2 ways. One method is repeating data from every nth row starting with 1st row and the other method is repeating data from every nth row starting with nth row. Now we will see examples for both cases.


1. Starting with 1st Row

In order to solve this example, we have taken a dataset like below where we have Student ID numbers of 8 students and their Total Marks. Now we will copy every third (n=3) row from the column Student ID starting with the first row.

Suitable Ways to Repeat Formula in Every nth Row in Excel

In order to use this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=OFFSET($B$5,(ROW(B1)-1)*3,0)
  • Now, press Enter.
  • As a result, you will see an output like the image below where it will display the first Student ID (1612002) from the column Student ID.

Suitable Ways to Repeat Formula in Every nth Row in Excel

  • Second, select cell E5 and drag the Fill Handle to the entire column.
  • Finally, you will see an output like the below one where it will show display Student IDs from every third row from the first row.


2. Starting with nth Row

Like before, we have taken the same dataset where we have Student ID numbers of 8 students and their Total Marks. Now we will copy every third (n=3) row from the column Student ID starting with the third (n=3) row.

Suitable Ways to Repeat Formula in Every nth Row in Excel

In order to use this method, follow the steps below.

Steps:

  • First, select cell E5 and type the following formula:
=OFFSET($B$5,(ROW(B1)*3-1),0)
  • Now, press Enter.
  • As a result, you will see an output like the image below where it will display the third Student ID (1612014) from the column Student ID.

Suitable Ways to Repeat Formula in Every nth Row in Excel

  • Second, select cell E5 and drag the Fill Handle to the entire column.
  • Finally, you will see an output like the below one where it will show display Student IDs from every third row from third row.

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


Things to Remember

  • Use of Fill Handle is the easiest way if you want to repeat formula in every nth row in Excel. When you have a large dataset you can use the Fill Handle by double clicking it rather than dragging
  • You can repeat any kind of formula for any n value by using the above-described methods.

Conclusion

Hence, follow the above-described methods. Thus, you can easily learn how to repeat formula in every nth row in Excel. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo