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.
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
- 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.
- 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.
- 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.
Read More: 4 Quick Ways to Insert Blank Row After Every Nth Row in Excel
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)
- After that, use the Fill Handle icon and double-click.
- 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.
🔨 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
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.
- 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
- 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.
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.
- 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.