# Excel Formula to Insert Rows between Data (2 Simple Examples)

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.

## Insert Rows between Data with Excel Formula: 2 Simple Examples

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

Sometimes we need to insert blank rows after every N-th row. In such a case, we can use an Excel formula easily. For example, I will use the combination of the 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}

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

Sometimes we need to insert a total rowÂ 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.

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.

## Conclusion

In the above article, I have tried to discuss several examples of inserting 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

<< Go Back to Insert Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF