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)**