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.