Insert multiple rows

How to insert multiple rows in a worksheet using Excel, VBA and Shortcut methods

METHOD 1. Insert multiple rows by selecting entire rows

EXCEL

Select the number of rows you want insert > Right-click anywhere on the selected rows > Click Insert

1. Select the number of rows you want to insert.
Note: in this example we are inserting three new rows in rows 2, 3 and 4. The new rows will be inserted in the same rows that you have selected. To select entire rows, either click on the first row number and drag down until you reach the number of rows you want to insert. Alternatively, select the first cell of the row, press and hold the Ctrl and Shift keys and press the Right key, then release the Ctrl key (still holding the Shift key) and press the Down key to select the number of new rows you want to insert. 		Select multiple rows

2. Right-click anywhere on any of the selected rows and click Insert. Right-click and click Insert

METHOD 2. Insert multiple rows using the ribbon option

EXCEL

Select multiple cells > Home tab > Cells group > Insert > Insert Sheet Rows

1. Select the cells where you want to insert new rows.
Note: in this example we are inserting three new rows in rows 2, 3 and 4. The new rows will be inserted in the same rows of the selected cells. You can select multiple cells across separate rows and columns (e.g. B4, E7, G9) which will insert new rows in rows 4, 8 and 11. The reason why Excel doesn't insert the new rows in the nominated row references is because the selected cells, after the first selected cell, would have shifted down. In this example Excel will inserts the first row in row 4, then moves to the next cell's reference which has shifted down by one row and inserts a new row in row 8. With the third row, G9, it would have shifted down by two rows and hence the new row will be inserted in row 11.		 Select multiple cells across multiple rows

2. Select the Home tab. Select Home tab - Excel

3. Click Insert in the Cells group.
4. Click Insert Sheet Rows.		 Click Insert and click Insert Sheet Rows

METHOD 3. Insert multiple rows using the cell option

EXCEL

Select multiple cells > Right-click on any of the selected cells > Insert > Entire row > OK

1. Select the cells where you want to insert new rows.
Note: in this example we are inserting three new rows in rows 2, 3 and 4. The new rows will be inserted in the same rows of the selected cells. You can select multiple cells across separate rows and columns (e.g. B4, E7, G9) which will insert new rows in rows 4, 8 and 11. The reason why Excel doesn't insert the new rows in the nominated row references is because the selected cells, after the first selected cell, would have shifted down. In this example Excel will inserts the first row in row 4, then moves to the next cell's reference which has shifted down by one row and inserts a new row in row 8. With the third row, G9, it would have shifted down by two rows and hence the new row will be inserted in row 11.		 Select multiple cells across multiple rows

2. Right-click on any of the selected cells.
3. Click Insert		 Right-click on any of the selected cells and click Insert

4. Select the Entire row option and click OK group. Select Entire row and click OK

METHOD 1. Insert multiple rows using VBA by selecting a multiple cells

VBA

Sub Insert_Multiple_Rows()
'insert multiple rows as rows 2, 3 and 4
Worksheets("Sheet1").Range("B2:B4").EntireRow.Insert

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

ADJUSTABLE PARAMETERS
Rows Selection: Select where you want to insert new rows by changing the row number references ("B2:B4"). The row selection doesn't need to be in a single range. You can replace the range reference with, for example, ("B4,E7,G9") which will insert new rows in rows 4, 8 and 11. The reason why Excel doesn't insert the new rows in the nominated row references is because it initially selects the specified cells and then inserts the first row in row 4, then moves to the next cell's reference which has shifted down by one row and inserts a new row in row 8. With the third row, G9, it would have shifted down by two rows and hence the new row will be inserted in row 11. You can also change the column reference to any column as this will have no impact on where the new rows will be inserted.
Worksheet Selection: Select the worksheet where you want to insert new rows by changing the worksheet name ("Sheet1").

METHOD 2. Insert multiple rows using VBA by selecting an entire rows

VBA

Sub Insert_Multiple_Rows()
'insert multiple rows as rows 2, 3 and 4
Worksheets("Sheet1").Range("2:4").EntireRow.Insert

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

ADJUSTABLE PARAMETERS
Rows Selection: Select where you want to insert new rows by changing the row number references ("2:4"). The row selection doesn't need to be in a single range. You can replace the range reference with, for example, ("4:4,7:7,9:9") which will insert new rows in rows 4, 8 and 11. The reason why Excel doesn't insert the new rows in the nominated row references is because it initially selects the specified rows and then inserts the first row in row 4, then moves to the next row's reference which has shifted down by one row and inserts a new row in row 8. With the third row, row 9, it would have shifted down by two rows and hence the new row will be inserted in row 11.
Worksheet Selection: Select the worksheet where you want to insert new rows by changing the worksheet name ("Sheet1").

Insert multiple rows using a Shortcut

SHORTCUT

WINDOWS SHORTCUT
Method 1

Ctrl
Shift
+

Method 2

Ctrl
+

NOTES

To insert multiple rows using these shortcut methods you will need to select entire rows. If you select an individual cell or a range of cells and action this shortcut an Insert dialog box will appear and you will need to select Entire row and click OK.

The Plus Sign key in the first method refers to the key on the top of the keyboard. The Plus Sign key in the second method refers to the key to the right of the keyboard, which some devices will not have. The reason why the first method requires the use of the Shift key is because the Plus Sign key is used for both Plus and Equal Signs, therefore, to activate the Plus Sign you are required to use the Shift key.

Explanation about how to insert multiple rows

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to insert multiple rows in a worksheet using Excel, VBA and Shortcut methods.

Excel Methods: Using Excel you can insert multiple rows by selecting entire rows, multiple cells and using a ribbon or cell option.

VBA Methods: Using VBA you can insert multiple rows by referencing to a multiple cells or entire rows.

Shortcut Methods: Using a Shortcut you can instantly insert multiple rows by selecting entire rows where you want to insert new rows and actioning the shortcut.

ADDITIONAL NOTES
Note 1: Inserting new rows will move the existing rows that are below the new rows downward.
Note 2: To insert multiple rows, for example three new rows, you will need to ensure that the every cell in the last three rows of the worksheet are clear of any content, otherwise Excel will not permit you to insert the rows.

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert a column How to insert a single column in a worksheet using Excel, VBA and Shortcut methods
Delete a column How to delete a single column in a worksheet using Excel, VBA and Shortcut methods
Delete multiple columns How to delete multiple columns in a worksheet using Excel, VBA and Shortcut methods
Insert a row How to insert a single row in a worksheet using Excel, VBA and Shortcut methods
Insert multiple columns How to insert multiple columns in a worksheet using Excel, VBA and Shortcut methods