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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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)

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

  • 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.

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

  • 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.

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

  • 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.

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

  • 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.

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

  • 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 Multiple Blank Rows in Excel


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.

Add Blank Rows between Data Using Helper Column in Excel

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

Add Blank Rows between Data Using Helper Column in Excel

  • Once you hit Enter, the following will be the output.

Add Blank Rows between Data Using Helper Column in Excel

  • Then, add another Helper column to the dataset and type the following formula in Cell E7.
=B7=B6

Add Blank Rows between Data Using Helper Column in Excel

  • Consequently, we will get the below result.

Add Blank Rows between Data Using Helper Column in Excel

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

Add Blank Rows between Data Using Helper Column in Excel

  • 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.

Add Blank Rows between Data Using Helper Column in Excel

  • 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.

Add Blank Rows between Data Using Helper Column in Excel

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

Add Blank Rows between Data Using Helper Column in Excel

  • 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: Insert Multiple Rows After Every Other Row in Excel


Download Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo