3 Ways to Insert Rows Between Data Using Formula in Excel

When dealing with a large dataset, we need to insert blank rows between data. There are myriad ways to insert rows between data. However, this article describes how to insert rows between data using formula in Excel.


📁  Download Excel File

Download the following Excel workbook to realize the topic more clearly.


Learn to Insert Rows Between Data Using Formula in Excel with These 3 Easy Approaches

Today we will use the following sample dataset to insert rows between data using formula in Excel. The dataset contains car models, manufacturers, and numbers of units sold.


Approach

1. Using Basic Formula and Helper Columns

In our first approach, we will use basic formula to insert rows between data with the help of helper columns.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell E7 and Enter the formula below:

=C6=C7

insert rows between data using basic formula

  • Next, select cell F8 and type in the following formula and press Enter:

=C8=C7

  • Afterward, select cells E7:F8. Then move to the bottom right corner of cell F8 to use the Fill Handle icon and double-click.

  • We have now two lists of TRUE and FALSE.

 insert rows between data applying basic formula

  • After that, navigate to Home tab and click Find & Select drop-down menu.

  • Then select Find.

  • A Find and Replace pop-up will appear on the screen momentarily. You can just press Ctrl+F concurrently to open the pop-up box easily.
  • Later, in the Find what box, write FALSE. Select Values from Look in drop-down box. Then, click on Find All.
  • Next, press Ctrl+A to select all the instances and Close the dialog box.

insert rows between data implementing basic formula

  • Now, right-click on any of the selected cells and select Insert.

  • From the tiny pop-up box, click Entire Row button and OK.

  • Voila! We inserted a blank row after every manufacturer.

how to insert rows between data administering basic formula

📕 Read More: 4 Quick Ways to Insert Blank Row After Every Nth Row in Excel


Approach

2. Merging MOD and ROW Functions

Now, we will discuss how to insert rows between data with the combination of MOD and ROW functions.

 ⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, select cell E6 and Enter the formula below:

=MOD(ROW(C6)-ROW($C$5)-1,3)

how to insert rows between data combining mod and row functions to create formula

  • After that, use the Fill Handle icon and double-click.

how to insert rows between data merging mod and row functions to create formula

  • Next, press Ctrl+F to open Find and Replace dialog box.
  • Type 0 in Find what box and click Find All.
  • Then, press Ctrl+A to select all the values. Close the dialog box.

  • Afterward, you can deselect the first instance by pressing Ctrl and left-click together, if you do not want an empty row just below the headers. Then, right-click on any of the selected cells and select Insert.

  • Now, select Entire Row button from the tiny pop-up box and click OK.

  • Done! Consequently, we inserted rows between every manufacturer.

how to insert rows between data coalescing mod and row functions to create formula

🔨 Formula Breakdown

MOD(ROW(C6)-ROW($C$5)-1,3)

👉  ROW($C$5) will return 5 as the row number of cell C5. ROW(C6) will return 6.

👉  ROW(C6)-ROW($C$5)-1 will return 0 as the number and 3 as the divisor for MOD function. We wrote 3 as we want to insert rows after 3 rows.

📕 Read More: 4 Easy Ways to Insert a Total Row in Excel


Approach

3. Executing VBA Code

Lastly, we will execute an elementary VBA code to insert rows after every 3 rows.

⬇️⬇️ STEPS ⬇️⬇️

  • To start with, select cells B6:D16 and navigate to Developer tab to select Visual Basic.

how to insert rows between data using formula and executing vba code

  • A Microsoft Visual Basic for Applications window will open immediately.
  • Next, select Module from Insert tab.

  • Instantly, a code window will open. Copy and paste the following code in the window and press Ctrl+S concurrently to save the code.
Sub InsertRowsBetweenData()
Dim r As Range
Dim c As Integer
Dim j As Integer
Set r = Selection
c = r.EntireRow.Count
For j = 1 To c / 3
ActiveCell.Offset(3, 0).EntireRow.Insert
ActiveCell.Offset(4, 0).Select
Next j
End Sub

how to insert rows between data using formula and embedding vba code

  • Later, move back to the VBA sheet and click Macros from Data tab.

  • Run the code from the dialog box.

  • There you go! Consequently, we inserted rows after every 3 rows.

how to insert rows between data using formula and executing vba code

📕 Read More: 7 Quick Ways to Insert a Row Within a Cell in Excel


How to Insert Multiple Rows Between Data Automatically in Excel

Now, we will use the same sample dataset to show how to insert multiple rows between data automatically using keyboard shortcuts.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell above which you want to insert multiple rows. In our case, we selected cell B9.
  • Next, press Alt+I to activate the Office access key.
  • Later, press R to insert a single row.

how to insert multiple rows automatically using keyboard shortcuts

  • After that, keep pressing F4 to add any number of rows. We inserted two more rows thus we pressed F4 twice.


📝  Takeaways from This Article

📌  Firstly, we demonstrated how to insert blank rows between data using basic formula.

📌  Next, we showed how to coalesce MOD and ROW functions to insert rows.

📌  Later, we executed an easy VBA code to insert rows after every 3 rows.

📌  Finally, we inserted multiple columns automatically using keyboard shortcuts.


Conclusion

That concludes the discussion for today. These are some convenient methods to insert rows between data using formula in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.

(Visited 42 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo