How to Insert Multiple Blank Rows in Excel (4 Easy Ways)

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 After Every Other Row 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,

Read More: How to Insert Multiple Rows 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 or keyboard shortcuts to insert new rows.

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 total blank rows 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,  

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,

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 Blank Row After Every Nth Row in Excel
• Excel Formula to Insert Rows Between Data
• How to Insert Rows in Excel Automatically
• Excel Fix: Insert Row Option Greyed Out in Excel
• Cannot Insert Row in Excel

<< Go Back to Insert Rows | Rows in Excel | Learn Excel