In this article, I will discuss how you can use an excel formula to insert rows between data. Often working with **Microsoft Excel**, we need to insert single or multiple rows in our dataset. I will describe two easy methods to add blank rows between excel data.

## Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

## 2 Examples to Insert Rows between Data with Excel Formula

### 1. Excel Formula with MOD & ROW Functions to Insert Rows between Data

Sometimes we need to **insert blank rows** into our data after every** N**-th row. In such a case, we can use an excel formula easily. For example, I will use the combination of** MOD **and **ROW** functions to insert rows between data. Suppose we have the below dataset containing several people’s names and ages.

Now I will insert blank rows after every **3** rows in this dataset. Follow the below steps to perform the task.

**Steps:**

- First, add a helper column to the parent dataset and type the below formula in
**Cell D5**. Next press**Enter**.

`=MOD(ROW(D5)-ROW($D$4)-1,3)`

- As a result, we will get the below output. Use the
**Fill Handle**(**+**) tool to copy the formula to the rest of the cells.

- This is the result we receive after applying the
**Fill Handle**tool. Now, select the helper column and press**Ctrl + F**to bring the**Find and Replace**dialog box.

- Then from the
**Find and Replace**dialog, enter zero (**0**) in the**Find what**box, click on Options, choose**Values**from the**Look in**drop-down, and click on**Find All**.

- Upon clicking on
**Find All**, excel will return the references of cells in the helper column that contains zero. When the result appears, press**Ctrl + A**to select all of them. - Once you select the output of the
**Find and Replace**dialog, those cells will be selected in the dataset too. Close the**Find and Replace**dialog.

- After that, among all of the selected cells containing
**0**, unselect only the first one. Then, right-click on any of the selected cells and click on**Insert**.

- As a consequence, the
**Insert**dialog will appear, click on the**Entire Row**option, and press**OK**.

- Finally, we can see blank rows are added after every three rows.

🔎 **How Does the Formula Work?**

**ROW(D5)**

Here the **ROW **function returns the row number of **Cell D5 **which is:

{**5**}

**ROW($D$4)**

Now, the **ROW **function returns the row number of **Cell D4**:

{**4**}

**MOD(ROW(D5)-ROW($D$4)-1,3)**

Lastly, the **MOD **function returns the remainder when** 0 **is divided by **3**. Here **3** is the value of** N**. If you want to insert blank rows between every **4** rows, **N** will be **4**. The result is:

{**0**}

**Read More:** **How to Insert Row in Excel ( 5 Methods)**

**Similar Readings**

**Excel Fix: Insert Row Option Greyed out (9 Solutions)****How to Insert Multiple Blank Rows in Excel (4 Easy Ways)****Move a Row in Excel (6 Methods)****How to Insert Multiple Rows in Excel (6 Easy Methods)****How to Insert a Row within a Cell in Excel (3 Simple Ways)**

### 2. Add Blank Rows between Data Using Helper Column in Excel

Sometimes we need to enter blank rows in a dataset when the value changes. Suppose we have a dataset containing fruit names and their order quantities. There are three types of fruits in my dataset and similar types of fruits are written in sequences. But at first glance, you cannot tell immediately at what rows a fruit’s name is changed. But if you can insert a blank row between the changing data, it will be easier to separate each fruit type.

To do the task follow the below steps.

**Steps:**

- First, insert a helper column to the parent dataset and type the below formula in
**Cell D6**.

`=B6=B5`

- Once you hit
**Enter**, the following will be the output.

- Then, add another
**Helper column**to the dataset and type the following formula in**Cell E7**.

`=B7=B6`

- Consequently, we will get the below result.

- Now, select
**D6:E7**and drag the**Fill Handle**down, you will get the below result.

- After that, select both
**Helper 1**and**Helper 2**columns and press**Ctrl + F**to get the**Find and Replace**dialog. - When the dialog comes up, type
**FALSE**in the**Find what**box, choose**Values**from the**Look in**drop-down menu, and click on**Find All**.

- As a result, we will get the cell that contains
**FALSE**. Use**Ctrl + A**to select all the results and close the**Find and Replace**dialog.

- As have selected all the cells that contain
**FALSE**, those cells will be selected in the main dataset too.

- Right-click on the selection and select
**Insert**.

- When the Insert dialog comes up, click on the
**Entire Row**and press**OK**.

- In conclusion, we can see a blank row is added whenever the fruit names change.

**Read More:** **Excel Macro to Add Row to the Bottom of a Table**

## Conclusion

In the above article, I have tried to discuss several examples to insert rows between data with Excel formula elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**Related Articles**

**VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)****Insert Rows in Excel Based on Cell Value with VBA (2 Methods)****How to Insert a Total Row in Excel (4 Easy Methods)****Macro to Insert Multiple Rows in Excel (6 Methods)****Cannot Insert Row in Excel (Quick 7 Fixes)****How to Insert a New Row in Excel (Top 5 Methods)**