Sometimes, you may come across a situation where you want to deal with lots of data and as it updates with time, you may need to insert multiple empty rows in your workbook. If you are facing issues to insert multiple blank rows in Excel, you are in a right place. This article will train you how to insert multiple blank rows in Excel in 4 simple and effective ways.
Let’s first introduction to the dataset. We have the following dataset which contains 5 rows without headings. Our goal is to insert multiple rows.
1. Selecting Multiple Rows and Insert an Equal Blank Rows in Excel
This method can insert an equal number of blank rows as you select. In this example, we want to insert 2 rows above row 7. Now, follow the steps below.
Steps:
- Select the 7th and 8th rows > press Right-click > choose the Insert option from the Context menu.
Here is the result,
Read More: How to Insert Multiple Rows in Excel
2. Using Name Box to Insert Multiple Blank Rows in Excel
This technique can allow you to insert multiple blank rows even in 1000s in Excel. In this example, we will insert 3 rows below the 5th row. Now, follow the steps below.
Steps:
- Go to the Name Box > In the Name box, type the values in the format “Initial row: Final row”. In this example, type 6:8. It will select the rows 6th to 8th.
- Now, press Right-click on the selected rows, choose Insert.
Finally, here is the result,
Similar Readings
- How to Insert Rows in Excel Automatically
- How to Insert Multiple Rows After Every Other Row in Excel
- Cannot Insert Row in Excel
3. Inserting Multiple Blank Rows in Excel Using Hotkeys
If you don’t want to do Right-clicks while you are inserting multiple blank rows, there are two Hotkeys you can use.
3.1 Pressing CTRL+SHIFT+Plus Keys
Follow the steps below.
Steps:
- By using row numbers on the left, select the number of rows equal to the number of blank rows you need to insert. In this example, the 6th to 8th rows are selected below the 5th row.
- Now, you need to press CTRL+Plus on the numeric part of your keyboard. Otherwise, if you want to use the “+” sign from the main keyboard, press CTRL+SHIFT+Plus. 3 blank rows will be inserted below the 5th row immediately.
Here is the result,
3.2 Pressing ALT+I+R Keys
Follow the steps below.
Steps:
- By using row numbers on the left, select the number of rows equal to the number of blank rows you need to insert. In this example, the 6th to 8th rows are selected below the 5th row.
- Now, you need to press ALT+I+R successively on your keyboard. 3 blank rows will be inserted below the 5th row immediately.
Here is the result,
Read More: Shortcuts to Insert New Row in Excel
4. Applying VBA Macro to Insert Blank Rows
When you are dealing with a large dataset on an Excel sheet that you want to use regularly, you could automate row insertion using VBA. You just need to follow the steps below.
Steps:
- Go to Developer tab > Visual Basic > Insert > Module. A Module window will pop up.
If you don’t have the Developer tab, you can also get to the Visual Basic window by pressing the ALT+F11 keys.
- Write down the following code in the Module window.
Sub InsertBlankRowsAtCursor()
Answer = InputBox("Input the number of rows to insert (Do not exceed 200)")
NumberofLines = Int(Val(Answer))
If NumberofLines > 200 Then
NumberofLines = 200
End If
If NumberofLines = 0 Then
GoTo EndInsertLines
End If
Do
Selection.EntireRow.Insert
Count = Count + 1
Loop While Count < NumberofLines
EndInsertLines:
End Sub
- Exit the Code window.
- Now, again go to Developer > Macros or press ALT+F8. Select the Macro we just made and click on Options.
- In the shortcut key box, use CTRL+SHIFT+H. Press OK.
- Go back to the worksheet and select a cell (Cell B6) below which you need to insert blank rows.
- Press the shortcut key (CTRL+SHIFT+H), and we will see a pop-up window asking us to type the number of rows we like to insert. In this example, type 5 and press ENTER.
Finally, here is the result,
Read More: Macro to Insert Multiple Rows in Excel
Download Practice Workbook
Download the following Excel file for your practice.
Conclusion
In this tutorial, I have discussed 4 quick ways to insert multiple blank rows in Excel. I hope you found this article helpful. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Insert Row Below in Excel
- How to Insert a Row Within a Cell in Excel
- How to Insert Blank Row After Every Nth Row in Excel
- How to Insert a Total Row in Excel
- Excel Formula to Insert Rows Between Data
- Excel Fix: Insert Row Option Greyed Out in Excel
- VBA to Insert Row in Excel
- Excel VBA: Insert Row with Values
- Insert Rows in Excel Based on Cell Value with VBA
- VBA Macro to Insert Row in Excel Based on Criteria
- Excel Macro to Add Row to Bottom of a Table
- Macro to Insert Multiple Rows in Excel
- Macro to Insert Row and Copy Formula in Excel
