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

The tutorial will teach you how to insert new rows in Excel with shortcuts, ribbon buttons and context menu, add blank rows between existing lines, and more.

Inserting rows in Excel is a routine task that many users encounter daily. While adding a single row is relatively simple, inserting multiple rows in the right places can sometimes be a challenge. In this article, we will explore different methods to insert multiple rows in Excel, catering to both shortcut-oriented users and those who prefer using standard menus. Additionally, we'll delve into inserting blank rows between data automatically.

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. Insert multiple rows in Excel.

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.
Another way to insert multiple rows between data in Excel.

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.
Formatting options for inserted rows.

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.
Add new rows in Excel using the ribbon.

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

Tip. When you need to insert a large number of rows in Excel, you can use the Name Box to quickly select the required range. For example, to insert 50 new rows starting from row 100, type "100:150" in the Name Box and press Enter. This will select the specified rows. Afterward, you can use any preferred method, such as the ribbon button, right-click menu, or shortcut, to insert the new rows. For more details, see How to select multiple rows using Name box.

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. Insert row shortcut in Excel.

Tip. If you need to add a significant number of rows, you can take advantage of the F4 button or the Ctrl + Y shortcut, which repeat the last action. For example, to add 100 empty rows, you can select a range of 10 rows, use the inserting rows shortcut to add 10 rows, and then just press F4 or Ctrl + Y ten times to repeat the insertion, resulting in a total of 100 empty rows.

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:

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

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.
Add a row to an Excel table.

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.

Note. Unlike working with ranges, the table functionality allows for the insertion of new rows only within a specific table. This can be particularly helpful when you have multiple datasets on the same sheet, and you want to add rows to a specific table without impacting data to the left or right.

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 new rows below in Excel.

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. Populate a helper column with sequential numbers.
  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. Sort the data set by the helper column.
  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. A new empty row is inserted between each existing row.

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:

VBA code to insert every other row in Excel
Sub InsertEveryOtherRow() Dim rng As Range Dim lastRow As Long Dim i As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' Get selected range Set rng = Selection lastRow = rng.Rows.Count + rng.Row ' Define a loop for each row in the range, starting from the last row For i = lastRow To rng.Row + 1 Step -1 ' Insert a blank row after each row Rows(i).Insert Shift:=xlShiftDown Next i Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

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.
Insert a blank every other row with VBA.

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.

That's how to insert rows in Excel. Whether you need to add a single row, multiple rows, or blank rows between existing data lines, Excel provides a variety of methods to suit your preferences. So go ahead, explore these techniques, experiment with their variations, and excel in your data-driven endeavors :)

Practice workbook for download

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

131 comments

  1. This is brilliant to assign a number to each row and then sort - so simple. Genius.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)