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

131 comments

  1. Its very useful.
    Thanks !

  2. i have data around 1 lack line items where as i need to inset the line for each 2000 line items pls guide me how to insert row for each 2000 line items in excel

    • Can some one pls support me here

      • It is possible to solve your problem by writing VBA code specifically for you. But that goes beyond the help we provide on this blog.

        • Thank you for reply, iam ok with VBA coding, pls share coding so that i can apply on my file

          other wise share me some other solution like formula or functioning

          • I think you can simply change the step from -1 to -2000 in the example given above in the article.
            It may need some range checking first though - I don't know how Excel reacts to this kind of range errors.

  3. Helper column is pure genius, saved me hours of manually inserting lol!

  4. Really it works great, I tried with 2nd method for large number of data. Thanks

  5. Happy New Year,

    I have a spreadsheet of data about 40-50 pages or more
    It is a detailed list of all activities on all projects over the entire month for 10+ people
    I need to separate every project by two blank lines

    Notes:
    No project has the same amount of activities - the number of lines could be 1 or 230
    I have unique project numbers, but those numbers are not consecutive
    I have client names - but many of those have various project numbers

    I am a beginner in Excel - but wondered if a query or macro would be the way to go?

    Thank you,
    MJ

  6. Thanks it worked

  7. Thank you for this tip So helpful! Lots of good info here. Ctrl-Click really turned on a light bulb.

    I had a person ask me about adding 2 rows after every two rows. With this information and a little test, we found a pretty easy way to do it.

    Leave the first two rows alone
    Select the 3rd Row
    Shift Select the 4th row
    then alternate Ctrl-Click and Shift-Click all the way down
    Right-click and insert. Like magic.

  8. I tried in Master data.. it's cleared my work in easy method. Thank you so much for sharing this kind of workouts.

  9. If I had a shortcut to 'THANK YOU' so much that you have thought the shortcut, then I would have delivered so much 'THANK YOU's here which would have even caused this website to go down with the abundance of 'THANK YOU's.... :)

  10. Thanks for this article.
    This is an awesome solution..

  11. Thank you. Fab tips.

  12. I am new to Excel
    I am working on an Excel spreadsheet
    I inserted some lines and the formulas wasn’t copied. Formulas are missing from some of my existing lines. How do I get the formulas to all to all the lines

  13. Hi,

    i need add missing rows as per below example.

    7
    8
    9
    12
    14
    18
    22

    thank you

  14. Once again, Ablebits saves me a HUGE amount of work. I often have to work with ginormous spreadsheets - and your product saves me so much time and effort. Thanks. Suggestion - at some point create a tool that will do just what this is article is all about. I know about the empty row removal tool (wonderful) but a row add tool would be great too, especially with large numbers of rows. The info in this article is helpful too.

  15. HI.I need to copy 2 lines which have some data and insert between every line of data. Is there a way to do this rather than copying and pasting up to 700 times? The spreadsheet can vary in length from 10 to 700 lines of data to insert these 2 lines between. Thanks

  16. Hi, I would like to request your help on how to insert a rows between two rows which is not in an order for example one new row after data in the row number 3, than again after the date date in row number 10 and again after the data in the row number 40 and so on which is not in a definite pattern. It is very difficult to get this then the data is too big more than 10K+ rows. Is there any way it could be handled in a simple way... Thank you!!

  17. Hi,
    I would like to request your help on how to insert a row immediately after the above row is filled with some value. Do you have some kind of formula.

    Thanks

  18. i have data in multiple columns. i want to transpose them in single column and multiple NEW rows.
    Like :-
    Dealer model
    ram a1 39 27 20 39 46 12 17
    kumar b1 46 22 21 29 33 33 37
    abhi c1 44 16 48 29 28 31 30
    rohit d1 28 50 45 49 18 20 45

    RESULT:-

    Dealer model
    ram a1 39
    ram a1 27
    ram a1 20
    ram a1 39
    kumar b1 46
    kumar b1 22
    kumar b1 21
    kumar b1 29
    kumar b1 33
    abhi c1 44
    abhi c1 16
    abhi c1 48
    abhi c1 29
    abhi c1 28
    abhi c1 31
    rohit d1 28
    rohit d1 50
    rohit d1 45
    rohit d1 49
    rohit d1 18
    rohit d1 20
    rohit d1 45

    Can you help ??

  19. I have a 30k line spreadsheet which I have sorted by column T. I would like to insert a blank row after every time the value in column T changes. On this blank line I would like to auto-sum the values in column S. Can you help?

  20. I would like to insert a new line in my Excel Spreadsheet. I would like line 6 to appear twice, one with the name "Robert Robinson" one with the name "Sheryl Robinson", his spouse. All other data would remain the same. I don't want Sheryl's information to be on line 7; I want two line 6s. Can you help me? Thank you!

  21. I would like to insert a new line in my Excel Spreadsheet. I would like line 6 to appear twice, one with the name "Robert Robinson" one with the name "Sheryl Robinson", his spouse. All other data would remain the same. I don't want Sheryl's information to be on line 7; I want two line 6s. Can you help me? Thank you!

  22. Thanks. Very Helpful

  23. Question. I have a spreadsheet with data that needs to be made into a chart. However, they would like very obvious spacing between some of the data so it looks like three separate data sets in one chart. The only way we could think of to do this was to add a blank column between the columns we wanted separate. The problem is when you show the Data Table, there are now black columns in that too. So, is there a way to do this so it doesn't look silly? Either adding space between columns (padding didn't work) or manually removing the empty column from the Data Table.

  24. I have 100 names one below the other , i need to 40 rows for all names.
    How do i do that?

  25. Wow, its like miracle for me, saved my 60 min

  26. Dude, thank you so much. You saved me so much time.

  27. I need to add a row in between which I now how to do it if these are blank rows.
    But...i need to add a row in between, all with the letter C in it (not blank rows).
    Please HELP!

  28. Wonderful ! Simple application of basics !
    Thank you

  29. You guys are awesome. My life and multiple career spreadsheets are little, but still imp to me. :)
    Thanks for making my day a whole lot better.
    xx
    Lizzi Tremayne Author

  30. I seek to insert as many no of rows as mentioned in Quantity column in a list (sample of which) as referred below
    Asset Cap-date Use Asset description Quantity
    100010 20-06-2014 94 SEZ Land 50
    200001 18-01-2002 60 Helipad and pathway at sit 1
    200004 09-04-2004 60 Drains 60

    How I can automate insertion of exact no of rows as mentioned in quantity column underlying each row.
    Thanks

  31. Hi,

    Can you please tell me how to add row(s) after a gap of rows in a large database in a go.

    E.g. I need to add a row in a large database after every 10th row. How to add ?

    Regards,
    Ghizali Ahmed

  32. how i can add 7 rows of each cell.

  33. Very helpful. Inserting blank rows after each row with sorting saved a lot of time.

  34. Thank you Maria!!

  35. Hi Maria,
    This is very helpful.
    How can I do If I do not want to insert new row all.
    I want to insert new row for certain cells.
    For instance, I have 100,000 items. And I want to insert new row under 300 items.
    How do I do that?
    Thanks in advance.

  36. please I have more than 500,000 data in a single column. I want the content in each column to be repeated 6x and an empty column to separate or mark the begin of each column...
    please how can I do this? it is seriously giving me headache, I can't go through the stress of doing it manually...
    thanks in advance.

  37. I've created an Excel workbook that has tabs for each month of the year and timesheets on each monthly worksheet for each employee. They include different projects and in alternating columns(one column for each date has the number of hours, the next column to the right of it has a letter to indicate the phase. What I would like to do is calculate for each project, how much time is being spent on each phase for the entire year.What's the best way to do this?

  38. I've created an Excel workbook that has tabs for each month of the year and several timesheets on each monthly worksheet for each employee. The timesheets include different projects and in alternating columns(one column has the number of hours, the next column to the right has a letter to signify the phase. What I would like to do is calculate for each project, how much time is being spent on each phase for the entire year. I'm not sure how to go about this. Is this something a Pivot Table would be suitable for?

  39. Sir/mam
    4 Rows after 1 blank row insert its possible for Microsoft excel please advise me

  40. Hi , I am working with a large dataset & want to insert 10 blank rows between each row. I tried to manipulate the vba code posted by Alexander as another poster seemed to have a similar problem, but I can't get it to work! Please help (code I tried to manipulate below):
    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

    Please advise! Thanks :-)

  41. this article of inserting rows in columns was very helpful

  42. Hello,
    I'm formatting a spreadsheet using List Data Validation in some cells and things like Date formatting in other cells for quicker fill out. This spreadsheet data will grow every time by adding a new row of information. Is there a way to automatically insert a new formatted row once a new row of information is filled? I always want to have just one empty formatted row below my last filled-out row without having to copy and paste row formatting every time.

    Thank you very much!

  43. How do I insert blank rows after rows of each product when rows of data are different per product in a series of many products?
    Product no Description Price
    00001 SR1500 Gl77 1000.00
    00001 SR1530 LGA2 50.00
    00001 SR1250 GHV1 500.00
    00002 SR1325 BNJ3 1200.00
    00002 SR2001 NBD4 1356.00
    00003 SR2658 MNJ1 4589.00
    00003 SR1236 NBH9 1254.00

  44. Thank you so much. 2nd method of entering rows multiple was much more help and save my time.

  45. how to insert 20 rows after filtered data

  46. Hello,

    I am preparing packing list in excel. For the details of every 1 container i need 1 row in my format of packing list. So if i entered the details for number of containers, rows should be automatically added or deleted as per my requirement. Can it be happen?

    • Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  47. Very helpful, thank you

  48. Thank you so much. You helped me like an angel.

  49. Thanks a lot for the blank lines inserting idea. Saved me a lot of time!

  50. The Helper column thing is such an ingenious workaround! Kicking myself, why didn't I think of that. lol

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