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 2. Total comments: 133

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

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

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

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

  5. Thanks. Very Helpful

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

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

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

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

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

  11. Wonderful ! Simple application of basics !
    Thank you

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

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

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

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

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

  17. Thank you Maria!!

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

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

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

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

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

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

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

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

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

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

  28. how to insert 20 rows after filtered data

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

  30. Very helpful, thank you

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

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

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

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

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

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

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

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

  39. Thankyou. This guide helping much

  40. Nice

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

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

  43. Genius and helpful thank you

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

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

  46. THANK YOU, both work!

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

  48. Thanks a lot your teaching was really useful.

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

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

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