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

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. Continue reading →

Comments page 3. Total comments: 133

  1. 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?

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

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

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

  5. 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!

  6. 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

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

  8. how to insert 20 rows after filtered data

  9. 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?

    1. 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.

  10. Very helpful, thank you

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

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

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

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

  15. How I can insert multiple rows (10)to multiples enteries (6)in once simultaneously.

  16. How I can insert multiple rows (15) for multiple enteries (5) at once simultaneously.

  17. WOW,

    Thank you this worked for me,
    "2.Now copy the series in the helper column and paste the range just below the last cell."

    I have been using different trial versions of software.
    but this has solved it.

    Thanks

  18. I have the following formula in my spreadsheet. I want to add 5 blank columns below - I'm trying to convert a horizontall organized spreadsheet into more vertical format.

    Family name spouse (2 names vertically stacked) Children (up to 5 vertically stacked)

    Here is the formula -

    =OFFSET('All Members Master'!E4,(ROW(A1)-1)*1,0,1,1)

    The family name needs 4 blank spaces below it to compensate for the space for up to 5 children.

    I'm pretty good with formulas but haven't used macros before.

    Thanks in advance.

  19. Thankyou. This guide helping much

  20. Nice

  21. Can anyone tell me how I can take the data below and create multiple rows for each column for the unique number. For Instance I need 3 rows for unique number 123, a row with Number, a row with current, and a row with amount.

    Unique Number Current Amount
    123 381.43 125.87 114.43
    1234 50.37 55.41 50.37
    12345 40.26 55.41 50.37
    123456 44.8 59.29 0
    234 647.31 194.19 176.54
    2345 324.71 78.77 71.61
    23456 324.71 238.7 71.61
    234567 549.01 78.77 71.61
    345 549.01 238.7 71.61
    3456 42.99 65.71 59.74
    34567 385.95 84.91 77.19
    345678 385.95 308.76 77.19
    456 0 84.91 77.19
    4567 0 308.76 77.19
    45678 119.47 65.71 59.74

  22. Hi I know this is a very old thread. Is it still alive??

    Thanks for your article! My question is how can I insert or delete a row and the other data maintain its numerical order. For example, if I insert a new row between numbered rows 5 and 6, how can I make sure that the new data is now numbered #5 and all of the subsequent or following data shifts up by one space and reflects its new position? So, in this case what used to be #5 in the list is now numbered #6?

  23. Genius and helpful thank you

  24. Thank you so much dear. 2nd method of entering rows multiple was much of a help. otherwise I would have wasted ma whole day to do that.

  25. Thank you, the helper column did the job, plain and simple!

  26. THANK YOU, both work!

  27. Thanks for help. It's really helpful for my big data file.

  28. Thanks a lot your teaching was really useful.

  29. Thanks Guys... simple & crisp article which helped me save many hours and made my day... Appreciate your help

  30. I want insert at different location in data execel sheet. Insert row must be a particular named down row.

  31. I neeD to add two rows but no empty rows. something like this:

    Site Name Class Name Participant Name
    Childplus CLEARWATER CENTER 4A CLEARWATER Ambrocio, Natalie
    ELC
    DIF
    Childplus CLEARWATER CENTER 4A CLEARWATER Cordes, ALan
    ELC
    DIF
    Childplus DUNEDIN CENTER 4B DUNEDIN Lopez, Carlos
    ELC
    DIF

  32. THANKS

  33. i am trying to serial number 123 again 123 again 123 serial key plz send

  34. Hi,

    I have a list of accounts and month end dates, below is a sample:

    account end_date return
    xxx-xxx-xxxx-1 Friday, May 31, 2013 -0.67
    xxx-xxx-xxxx-1 Sunday, June 30, 2013 -0.36
    xxx-xxx-xxxx-1 Wednesday, July 31, 2013 3.44
    xxx-xxx-xxxx-1 Saturday, August 31, 2013 -2.23
    xxx-xxx-xxxx-1 Monday, September 30, 2013 2.89

    I am trying to insert rows for all of the days between each month end date. For example I want to insert a new row for June 1, June 2, June 3.... June 29. Can anyone help me out? Thanks.

  35. How do I create a series between each blank row I want to insert numbers 1 to 100 but I don't want to add each individually is there a short cut?

    1. Hello Christian,

      You can select multiple areas and fill it with numbers using the following macro:

      Sub FillSelectedAreas()
      Dim area As Range
      Dim cell As Range
      Dim i As Integer
      For Each area In Selection.Areas
      i = 1
      For Each cell In area
      cell.Value = i
      i = i + 1
      If i > 100 Then
      Exit Sub
      End If
      Next
      Next
      End Sub

  36. thanks its really Work.

  37. Hello. I have a spreadsheet that needs to have data entered every 6 months for many specific items. Is there an easy way to add a row after the last entry for each item in order to make room to add the newest data or do I have to manually insert a row every time for each item?
    Ex:
    A B C
    OW-1 7/22/14 36.21
    1/19/15 37.89
    7/27/15 43.87
    OW-2 7/22/14 46.19
    1/19/15 44.24
    7/27/15 51.89
    OW-3 7/22/14 35.14
    1/19/15 32.13
    7/27/15 36.57

    1. Hello Angela,

      I recommend you find and select all cells that contains "OW" (hit Ctrl+F shortcut).

      Then run the following macro to select entire rows of selected cells:

      Sub SelectEntireRows()
      Selection.EntireRow.Select
      End Sub

      Now, when the row selected, you can use the insert function described above.

    2. After posting my question, the format of the example shifted all of my data to to be left aligned. The item identifiers are in column A, dates in column B and data in column C (Note: the item identifier is only listed once at the first entry, therefore the proceeding rows only have data in column B and C.)

  38. I'm trying to download data to Excel 2010 but it stops and gives message that there's too much data. It is only allowing the 65K some rows. How do I get all my download into into one file? Thanx!!!!

    1. Hello Deirdre,

      Try to change a format of workbook.
      File > Options > Save > Save file in this format: Excel Workbook (*.xlsx)

      Please see more info:
      https://answers.microsoft.com/en-us/office/forum/office_2010-excel/my-excel-2010-only-has-65536-rows-how-do-i-change/16811354-abad-41eb-bc65-049d47cfc553

      it may also be useful:
      Excel cannot complete this task with available resources error, Excel 2010
      https://support.microsoft.com/en-us/kb/2655178

  39. It's always nice to find a solution that's both simple and innovative.

    I coded something before I read this article but your solution was much smarter.

    Thanks

  40. thanx for soln. can you pls help inserting columns in similar way.

  41. To insert a blank row after each row that houses the word "TOTAL" down through your spreadsheet until "GRAND TOTAL"

    1) At the top (under the MACRO TITLE), type "Dim inX As Integer" (exactly as written in the quotations).

    2) At the end (between your last line in your MACRO and "END SUB", but on a different line), copy and paste the following:

    inX = 2
    Do Until Range("C" & inX) = "Grand Total"
    If Right(Range("C" & inX), 5) = "Total" Then
    Rows(inX + 1 & ":" & inX + 1).Insert Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove
    End If
    inX = inX + 1
    Loop
    End Sub

  42. I am using MS Excel v10. I am trying to step into my V.B. / Macro and add an entry that will insert a blank row after each line showing Total! Please tell me there is an easy way to do this. I am doing it manually now and when you have multiple sheets it becomes burdensome. Can you help me? Thanks, R, Dave

  43. loved th method thanks for sharing

  44. Thank you

  45. 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

  46. 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.

  47. Thanks.
    Very helping.

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

    1. Hello, Phani,

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

  49. 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.

    1. Hello, Sabina,

      Most likely you need a macro or VBA. Sorry, I cannot help you with this.

  50. You're amazing!

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