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

Get FREE Advanced Excel Exercises with Solutions!

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!

## How to Repeat Formula in Every nth Row in Excel: 2 Easy Ways

There are 2 easy ways to repeat formulas in every nth row in Excel. One is by using Fill Handle and the other one is by combining MOD and ROW functions. 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.

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

Steps:

`=CONCATENATE(B5,",",B6)`
• Now, press Enter.
• As a result, you will see an output like the image below where it will display the concatenate form of the Student IDs of cells B5 and B6.

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

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

Read More: How to Repeat Multiple Rows in Excel

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

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 the concatenate form of the Student IDs of cells B5 and B6.

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

## Utilize a 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 OFFSET and ROW functions. We can do this task in 2 ways. One method is repeating data from every nth row starting with the 1st row and the other method is repeating data from every nth row starting with the 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 the 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.

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.

• 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 the nth Row

Like before, we have taken the same dataset where we have the 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.

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.

• 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 display Student IDs from every third row from third row.

## Things to Remember

• Use of Fill Handle is the easiest way if you want to repeat the 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.

Download Practice Workbook

You can download the Excel workbook from here.

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

Donâ€™t forget to drop your comments, suggestions, or queries in the comment section below.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF