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.


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.

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 Row in Excel ( 5 Methods)


Similar Readings


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.

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: 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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo