Md. Asaduzzaman
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.
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.
- 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.
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 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 (7 Easy Ways)
Similar Readings
- Repeat Text in Excel Automatically (5 Easiest Ways)
- Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)
- How to Repeat Rows in Excel at Bottom (5 Easy Ways)
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- How to Repeat Cell Value X Times in Excel (6 Easy Methods)
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.
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 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.
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 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
- How to Repeat Column Headings on Each Page in Excel (3 Ways)
- Select Column A as Titles to Repeat on Each Page
- How to Autofill in Excel with Repeated Sequential Numbers
- Repeat Rows in Excel When Scrolling (6 Suitable Ways)
- How to Count Repeated Words in Excel (11 Methods)
- How to Repeat Number Pattern in Excel (5 Methods)
