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

Get FREE Advanced Excel Exercises with Solutions!

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.

Insert Multiple Blank Rows in Excel (Sample Dataset)


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.

Insert Multiple Blank Rows in Excel by Selecting Rows

Here is the result,

Insert Multiple Blank Rows in Excel by Selecting Rows (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.

Insert Multiple Blank Rows in Excel Using Name Box

  • Now, press Right-click on the selected rows, choose Insert.

Finally, here is the result,


Similar Readings


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.

Insert Multiple Blank Rows in Excel Using CTRL+Shift+Plus

  • 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.

Insert Multiple Blank Rows in Excel Using ALT+I+R

  • 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.

Insert Multiple Blank Rows in Excel Using VBA

  •  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,

Insert Multiple Blank Rows in Excel Using VBA (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

Hafiz Islam
Hafiz Islam

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo