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)

Table of contents

133 comments

  1. Thank you

  2. Wonderful tip, this was quite helpful for me, if you need multiple rows in between copy the additional colom as said above....suposse the additional column is filled with 1 to 5 Numbers...and now if you need to add 5 rows, just copy and paste to last cell 5 times the result will so
    Example:
    Text Additional column
    A 1.00
    b 2.00
    c 3.00
    d 4.00
    e 5.00
    1.00
    2.00
    3.00
    4.00
    5.00

    After sorting with additional column you will find the result as below-
    A 1.00
    1.00
    b 2.00
    2.00
    c 3.00
    3.00
    d 4.00
    4.00
    e 5.00
    5.00

    So,I Find this as the quickest method and helped me a lot in my daily job.
    Thanks again

  3. Fantastic job with the last trick, I struggled with it so much to insert multiple rows between data. Very very handy!

    Good job.

    Thanks a ton.

  4. Thanks.
    Very helping.

  5. Special shortcut to insert rows with copied data in Excel if there's data to the down of your table

    • Hello, Phani,

      For us to be able to help you better, please send me a sample with your data in Excel.

  6. Hi I am trying to insert two lines after each break in name change ome of my clients I have multiple times and some only once. Like subtotaling I want to add lines after each name change? Please help.

  7. You're amazing!

  8. Thanks a lot for the trick it really saved my time

  9. I would like to add 3 empty rows after each vendor that I have listed in a column, How do I do this?

  10. No matter what, it greets me with "Excel cannot shift non-blank cells off of the worksheet....." :-(

  11. I have an excel file in which I have 20 sheets, all with formulas that are connected to the first page. I want to be able to insert/delete lines on the first page and that line on all sheets to be inserted or deleted as well, how do I accomplish this?
    Thanks,

  12. Its really helpful

  13. Is there a way to do this when the value changes - for example, I have 8 rows with "12345", then 8 rows of "54321" then 8 more of "92939". I would like to know if I can have a macro to insert blank rows between each "group" ?

    • I think this can work

      Sub InsertEvery8th
      Dim rowNo, rowStart, rowFinish, rowStep As Long
      Dim rng2Insert As Range
      rowStep = 8
      rowStart = Application.Selection.Cells(1, 1).Row + 1

      rowFinish = (ActiveSheet.UsedRange.SpecialCells( _
      xlCellTypeLastCell).Row * 2) - rowStart
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      For rowNo = rowStart To rowFinish Step rowStep
      ActiveSheet.Cells(rowNo, 1).EntireRow.Insert
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      End Sub

      • Thank you x 1 Million for this. Used it in 10 files varying depending on the file every 110th, 109th, or 99th. You have to add one more number than the total set you have. So if you want a Row added for every 99 entries, the value is 100.
        It also adds a row before the first row after the header row. I'm sure this would be easy to code out, for me as a luddite it was only 10 files with 22,000 entries, so not a big deal to remove manually afterwards.

  14. Colums B&C are Merged in rows 1-23, when I insert rows in this section the merged formating does not carry over using format same as above or below. Is there a setting that can correct this? Please advise.

  15. Hi

    Can you please tell me how do i insert multiple rows in odd numbers rows ( in row # 3,5,7,9......) in huge list of data? Thanks.

    • Please use the following macro to insert every other row from the first selected cell to the end of your data.

      Sub InsertEveryOtherRow()
      Dim rowNo, rowStart, rowFinish, rowStep As Long
      Dim rng2Insert As Range
      rowStep = 2
      rowStart = Application.Selection.Cells(1, 1).Row + 1

      rowFinish = (ActiveSheet.UsedRange.SpecialCells( _
      xlCellTypeLastCell).Row * 2) - rowStart
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      For rowNo = rowStart To rowFinish Step rowStep
      ActiveSheet.Cells(rowNo, 1).EntireRow.Insert
      Next
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
      End Sub

      If you are not very good at VBA, please see this article:
      https://www.ablebits.com/office-addins-blog/add-run-vba-macro-excel/

      • Thank you. This script helped me to insert multiple blank rows in between thousands of records I have. However, I had to run the macro multiple times to achieve my results. Is there a way to have custom selection? Say for example, I have 12 vendors and I want to insert 12 blank rows between each row. It would be nice if you have a parameter in the macro that inserts the rows based on that parameter.

  16. I have a huge spreadsheet containing unique names of genes in one of the columns, and information about the genes in the subsequent columns. These gene names sometimes have similar letters in their name. Eg. genes that start with the name LRRC and usually have a numerical value after them. I am trying to select the entire row of every gene whose name starts with LRRC (which are scattered in the spreadsheet) and then copy it into the new spreadsheet. How do I do this? I've tried finding all, selecting all of the genes, but this isn't time efficient, as there are often hundreds of genes with this same name...

    • Hello,

      Please try our Advanced Find and Replace add-in:
      Enter LRRC into the Find what field
      Click Find all
      Press Select cells and pick the option Select rows with all found entries.

  17. I m follow Add empty rows between multiple data lines in Excel sheet.
    Very quickly create format option.

    REgards,
    Jaheer

  18. Thank you very very very much!
    god bless you!
    I was badly in trouble and was killing myself to find a way for inserting multiple blank rows in a huge list of data. thank you dude :)

  19. I have a two page worksheet. How can I insert a new row in the first sheet but the last row in the first worksheet will automatically go down to the next page below the header in the second page? Please help

  20. I am inserting new rows to an existing spreadsheet and when I do that the row has no gridlines. Help?

    • File > Options > Advanced > Display Options for This Worksheet > Show Gridlines

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 :)