How to insert multiple rows in Excel: shortcut, right-click, VBA

How to insert multiple rows in Excel

There are a few easy ways to insert rows in Excel. Whichever method you use, before adding new rows, you need to choose the location and determine the number of rows to be inserted. Keep in mind the following rules:

• The new rows will always be inserted above the selected rows.
• The number of rows you select will determine the number of blank rows that will be added.

For instance, if you select 5 rows, Excel will insert 5 blank rows above the selected ones.

And here are the steps to insert multiple rows using the right-click menu:

1. Select the rows for insertion. To highlight multiple contiguous rows, click on the number of the first row you want to select, hold down the Shift key, and click on the number of the last row you want to select.
2. Right-click anywhere within the highlighted rows. This will open a context menu with various options.
3. Choose Insert from the context menu to initiate the insertion process.

That's it! Excel will promptly insert the desired number of rows above the selected position, creating the necessary space for your data.

And here is another quick way to insert multiple rows between data using the right-click menu:

1. Select the first few cells above which you want to add blank rows.
2. Right-click the selection.
3. From the context menu that appears, select the Insert command.
4. In the Insert menu, choose the Entire row option and click OK.

Whichever method you use, the Insert Options button will appear after inserting the rows. This button provides additional formatting options to help you customize the inserted rows, so they match the surrounding data:

• Format Same As Above. By selecting this option, the formatting, such as font style, cell borders, and background color, will be copied from the row above and applied to the newly added rows.
• Format Same As Below: Similarly, this option applies the formatting of the row below the inserted rows.
• Clear Formatting. This option removes any formatting from the inserted rows and makes them match the default formatting of your spreadsheet.

How to add rows in Excel using ribbon

Excel provides a convenient way to add new rows using the Ribbon menu. Here are the steps to follow:

1. Right below the insertion point, select as many rows as you want to add. To select adjacent rows, click and drag the cursor across the row numbers. To select non-adjacent rows, hold down the Ctrl key (Command key on a Mac) while clicking on the row numbers.
2. On the Home tab, in the Cells group, click on the Insert button.
3. Select Insert Sheet Rows from the drop-down menu.

Done! Excel will immediately insert multiple rows between your data.

Insert row shortcut in Excel

For those who prefer using keyboard shortcuts, Excel offers a quick and efficient option to insert rows:

1. Select the desired number of rows below the insertion point.
2. Use one of these keyboard shortcuts to insert new rows:
   • Ctrl + Shift + Plus on the main pad
   • Ctrl + Plus on the numerical pad

As with the previously discussed methods, the new blank rows will be inserted above the selected position.

Additionally, you can take advantage of the Access Key Combinations to insert multiple rows at once:

1. Select one or more lines below the insertion point.
2. Hold down the Alt key on your keyboard.
3. While holding the Alt key, press the I key, then the R key.

Here's what each key combination represents:

• Alt + I is the access key combination for the Insert menu in Excel.
• Alt + I + R is the access key combination specifically for inserting rows.

How to add a row in Excel

Adding a single row in Excel is a simple process that closely resembles inserting multiple rows. The steps are:

1. Select the row below where you want to insert a new row. To highlight the entire row, click on its number.
2. Choose any of the following techniques to add a new row:
   • Right-click and select Insert from the context menu.
   • Use the ribbon: Home tab > Insert > Insert Sheet Rows.
   • Use the Excel shortcut for inserting row.

As a result, a new blank row will be added above the selected row, exactly as needed.

How to add row to Excel table

To add new rows to a table in Excel, follow these steps:

1. Select any cell in the row(s) above which you wish to add a new row(s).
2. Right-click on the selected cell and choose Table Rows Above from the context menu.

As a result, the table will expand with one or more new rows, providing space for additional records.

If you select a cell in the last row of a table, you will have two options available:

• Table Rows Above - a new row will be added above the selected row.
• Table Rows Below - a new row will be added below the selected row.

With this functionality, managing and updating data in Excel tables becomes more efficient. For more information on working with tables, refer to How to add or remove rows and columns in a table for further guidance.

How to insert row below in Excel

While the traditional methods insert rows above the selected position, there is a little-known technique to add empty rows below. Follow these steps to accomplish this task:

1. Select the row below which you want to add new rows.
2. Locate the fill handle. Once the entire row is highlighted, you will notice a small green square at the bottom right corner of the selection. This green square is called the "fill handle".
3. Position your cursor over the fill handle and press and hold the Shift key. As you do this, the cursor will transform to two bars and arrows, indicating that you can insert new rows.
4. Left-click and drag the fill handle downwards while holding the Shift key to create as many new rows as you want. The number of rows inserted will depend on the distance you drag the fill handle.
5. Once you have created the desired number of new rows, release the mouse button.

That's it! Using this fill handle technique, you can insert any number of empty rows below the selected row.

Insert rows between each row in Excel

To add a line between every row in Excel, the initial instinct might be to manually select each row while holding down the Ctrl key, and then use the row insertion technique of your choosing. While this method may work for small datasets, it can become tedious and inefficient for larger worksheets.

Luckily, there is a more productive approach that involves utilizing a helper column and sorting. Here's a step-by-step guide:

1. Create a helper column and populate it with sequential numbers. First, insert a new column in your worksheet, preferably adjacent to your existing data. Then, using the AutoFill feature, populate the column with sequential numbers (1, 2, 3, 4, and so on). Ensure that the helper column has the same number of rows as your data.
2. Copy the entire number series and paste it below the last cell with data in the helper column. This will extend the sequential numbers to cover the additional rows.
3. Select the whole dataset and sort it by the helper column. For this, navigate to the Data tab and click the Sort button in the Sort & Filter group. In the Sort dialog box, choose the helper column as the primary sorting criteria and select Smallest to Largest.
4. Click OK and observe the results.

Excel will rearrange your dataset based on the values in the helper column. As a result, blank rows will appear between every row with data. You can now delete the helper column as it's no longer needed.

How to insert every other row in Excel with VBA

To insert blank rows in Excel between data automatically, you can utilize the following VBA code:

How to use the macro to insert every other row:

1. Add the code to your workbook. For the detailed steps, see How to insert VBA code in Excel.
2. Select the range where you want to insert empty lines between data.
3. Press Alt + F8 to open the macro dialog box.
4. Choose the InsertEveryOtherRow macro.
5. Click Run to execute the code.

As a result, the macro will automatically insert blank rows between every existing row within the selected range, saving you from the manual effort of individually selecting and inserting rows.

Practice workbook for download

VBA to add every other row in Excel (.xlsm file)