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:
- First, select cell C5 and type the following formula with the CONCATENATE function:
=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.
Read More: How to Repeat Formula for Each Row in Excel
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.
Read More: How to Repeat Rows in Excel Based on Cell Value
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.
Read More: How to Repeat Formula in Excel for Whole Column
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.
