Share via

How to insert many rows with many cells in between existing rows with many cells

robert jacobs 0 Reputation points
2026-03-16T18:55:43.9166667+00:00

I want to put blank row with many cells in between rows with cells with existing data, there are numerous of these

Microsoft 365 and Office | Excel | Other | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kal-D 7,365 Reputation points Microsoft External Staff Moderator
    2026-04-01T22:14:52.0533333+00:00

    Hi robert jacobs,

    If you need to insert many blank rows between existing data rows, doing them one-by-one via Insert Row quickly becomes impractical.

    Option 1 (No macro): helper column + sort (good for “blank row after every row”)

    1. Add a helper column next to your data and number the existing rows 1, 2, 3, …
    2. Copy the entire range (data + helper column) and paste it directly below the original.
    3. In the pasted copy, clear the data cells you want to become blank rows (leave the helper numbers).
      • If you want one blank row between every row, clear all non-helper cells in the pasted copy.
    4. Select the entire combined range (all columns), then Sort by the helper column (smallest to largest).

    Because each helper number appears twice (one real row + one cleared row), sorting groups them together so the blank row sits next to its original row.

    Option 2 (VBA, fastest if you do this often): insert one blank row between each row

    Sub InsertBlankRows()
    Dim i As Long, LastRow As Long
    'Change column 1 (A) to a column that is always filled in your data
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = LastRow To 2 Step -1
        Rows(i).EntireRow.Insert
    Next i
End Sub

    This inserts a blank row above each row from 2 down, which results in one blank row between each original data row. Working from the bottom up prevents row references from shifting during insertion.

    Note: For large sheets, it’s a good idea to save a copy before sorting or running a macro.

    Please let me know if this proves useful to you, or if you would like further assistance.

    I'm looking forward to your reply.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

  2. AI answer

    2026-03-16T18:55:53.2266667+00:00

    To insert many blank rows (with blank cells) between existing rows in Excel, use row insertion. Excel automatically creates the correct number of cells in each inserted row.

    For Excel on Windows:

    1. Decide where the blank rows should go (for example, between every existing data row).
    2. Select the row number below where the first blank row should appear.
    3. To insert one row at that position:
      • Go to Home > Insert > Insert Sheet Rows, or
      • Right‑click the row number and select Insert.
    4. To insert multiple rows at once:
      • Select the same number of existing rows as the number of blank rows needed (for example, select 5 row numbers if 5 blank rows are needed in that spot).
      • Use Home > Insert > Insert Sheet Rows or right‑click the selection and choose Insert.
      • Excel inserts that many blank rows, each with the correct number of cells, above the first selected row.
    5. Repeat at each location where blank rows are required.

    For Excel for Mac:

    1. Select the heading (row number) of the row above where the blank rows should be inserted.
    2. To insert multiple rows, select as many row headings as the number of blank rows needed.
    3. Hold down CONTROL, click the selected row headings, and choose Insert from the pop‑up menu.
    4. Excel inserts that many blank rows above the selection, each with blank cells across all columns.

    Notes:

    • All cells in the inserted rows are blank; existing data below shifts down automatically.
    • Any formulas that reference ranges including the insertion area adjust automatically to the new row positions.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.