Excel Insert a Row Every X Rows: Methods That Work

How-to basics · Updated November 19, 2026

To insert a blank row after every X data rows in Excel for Microsoft 365 on Windows, select the data rows without the header, run a short VBA macro, and enter the interval you want. For a small range, you can instead insert rows manually from the bottom upward; working upward prevents earlier insertions from changing the row positions you still need.

What “every X rows” means

Usually, “insert a row every X rows” means add a blank worksheet row between groups of X existing data rows. For example, if a list begins in row 2 and X is 3, the first blank row goes after rows 2, 3, and 4—that is, before the original row 5. The next blank row goes after the next three data records.

This is different from adding a blank row after every existing row, inserting a row at a fixed worksheet number, or spacing out a report for printing. Decide whether the header counts before you start. In the examples below, the header is not part of the data count: select the body rows only, and the macro inserts blank rows between groups without adding one after the final group.

Fastest method: use a VBA macro

A macro is the most practical option when the list is long or you need to repeat the task. It inserts actual worksheet rows, so the blank space applies across the sheet rather than only inside one selected column. The code below treats the selected range as the data body and asks how many data rows should be in each group.

  1. Save a copy of the workbook first. Macro changes cannot be undone with Ctrl+Z.
  2. Select a single, continuous range covering the data rows. For example, select A2:D101 if row 1 contains headings and the records occupy columns A through D.
  3. Press Alt+F11 to open the Visual Basic Editor. If the Developer tab is available, you can also choose Developer > Visual Basic.
  4. In the editor, choose Insert > Module, then paste the code below into the module.
  5. Return to Excel, press Alt+F8, select InsertRowsAtIntervals, choose Run, and enter the interval when prompted.
Sub InsertRowsAtIntervals()
    Dim ws As Worksheet
    Dim firstDataRow As Long
    Dim rowCount As Long
    Dim interval As Long
    Dim k As Long
    Dim r As Long
    Dim answer As Variant

    If TypeName(Selection) <> "Range" Then Exit Sub
    If Selection.Areas.Count > 1 Then
        MsgBox "Select one continuous data range."
        Exit Sub
    End If

    Set ws = ActiveSheet
    firstDataRow = Selection.Row
    rowCount = Selection.Rows.Count

    answer = Application.InputBox( _
        Prompt:="Insert a blank row after every how many data rows?", _
        Title:="Row interval", Type:=1)
    If answer = False Then Exit Sub
    If answer < 1 Or answer <> Fix(answer) Then
        MsgBox "Enter a positive whole number."
        Exit Sub
    End If

    interval = CLng(answer)
    For k = (rowCount - 1) \ interval To 1 Step -1
        r = firstDataRow + k * interval - 1
        ws.Rows(r + 1).Insert Shift:=xlDown
    Next k
End Sub

The loop works from the last group boundary toward the top. If you select rows 2 through 11 and enter 3, the macro inserts rows after the third, sixth, and ninth selected records. It does not add a trailing blank row after record 10. If the selected range contains fewer records than the interval, it makes no insertion.

Manual method for a short list

For a few groups, you may prefer not to enable or run a macro. Find the boundary rows from the bottom of the data upward. Right-click the row number immediately below a boundary and choose Insert, or use Home > Insert > Insert Sheet Rows. Repeat at each boundary, moving upward each time.

  • For groups of three in a list spanning rows 2 through 11, insert before the original rows 11, 8, and 5—in that order.
  • For groups of five, identify each fifth-record boundary, then insert above the next record, again starting with the lowest boundary.
  • If the desired blank line belongs at the end as well, insert it separately; the macro intentionally avoids a final trailing row.

Starting at the bottom matters because an insertion shifts rows below it down. If you start at the top, the later boundary numbers no longer refer to the same original records. When inserting an entire worksheet row, Excel creates space across all columns, which is usually what you want for a complete table-like list. It can also affect other content positioned beside or below that list, so check the surrounding worksheet first.

Choose the right approach for your data

Use VBA when you have many records, need repeatable results, or want to avoid manually locating every boundary. Use manual insertion when the list is short and the workbook’s macro policies make code inconvenient. If the goal is only to make a printed report easier to read, consider whether extra worksheet rows are necessary: page layout, cell borders, or alternating row formatting may provide separation without changing the data structure.

A formula is not a direct substitute for inserting worksheet rows. Formulas can display a transformed or spaced-out result in a separate area, but a spilled result is not the same as adding editable blank rows to the source. A macro or manual insertion is appropriate when downstream work depends on actual empty rows. If you need a repeatable report rather than a changed source list, keeping the original data intact and building a separate output may be safer.

Headers, Excel tables, and other worksheet content

Exclude column headings from the selected range. If a header is included, the macro counts it as a record and the first gap will appear at the wrong place. For a multi-column list, select all columns of the body across the same rows; the macro uses the selected row span to decide where to insert, even if only one column is selected, but selecting the full body makes your intent clearer.

Take extra care with an Excel Table. Tables are designed for continuous records and structured references, and inserting worksheet rows within or around a table can change its size or layout. Blank rows inside a table may interfere with filtering, sorting, totals, and formulas that expect a continuous set of records. If the purpose is visual spacing, consider keeping the table continuous and applying banded rows or borders instead. If blank records are a genuine requirement, make a copy and confirm the table and any linked formulas behave as intended.

Also inspect formulas, charts, named ranges, and notes below the selected data. Excel generally adjusts references when rows are inserted, but the exact effect depends on how those references are constructed and where the content sits. A row insertion is a structural change to the worksheet, not merely a formatting change.

Safety, saving, and undo

VBA edits are not reversed by Ctrl+Z. Save a separate copy before running the macro, particularly if the workbook contains important formulas, tables, or linked sheets. For a workbook that must retain the macro, save it in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm); saving as .xlsx does not retain VBA code.

Only run macros you understand and are permitted to use. The supplied routine inserts rows on the active worksheet, so verify both the selected range and the sheet name before running it. It does not delete or overwrite the selected records, but an insertion near the worksheet’s bottom row may fail because Excel has a fixed row limit. If the result is not right, close without saving the copy or restore the pre-run version rather than relying on Undo.

Excel for Mac and Excel for the web

Excel for the web does not run VBA macros. Use the manual insertion method there, or open the workbook in the desktop application. Excel for Mac supports VBA in the desktop app, though keyboard shortcuts and the location of some controls can differ. On Mac, use the Visual Basic Editor from the Developer tab if available; the keyboard shortcut is commonly Option+F11, and some keyboards require Fn as well. You can also run a macro from the Macros command on the Developer tab.

In Windows desktop Excel, Alt+F11 opens the editor and Alt+F8 opens the macro list. If those shortcuts are intercepted by your keyboard or system settings, use the Developer tab’s Visual Basic and Macros commands. The macro itself uses standard VBA row insertion and does not depend on a Microsoft 365-only worksheet function.

Common mistakes and how to avoid them

  • Counting the header: select only data records, unless you deliberately want the header included in the interval.
  • Inserting from top to bottom: later target positions shift. Work upward manually; the macro already processes boundaries bottom-up.
  • Selecting just part of a record: the macro uses selected rows, but entire worksheet rows are inserted. Select the full body range so the scope is obvious.
  • Expecting a gap after the last record: the macro inserts only between groups. Add a final blank row separately if your layout calls for one.
  • Running on a filtered list: hidden rows still belong to the selected range and are counted. The macro does not mean “every X visible records.” Clear or account for filters before running it.
  • Using it in a table without checking: test on a copy because table expansion and structured references can affect the result.

Frequently Asked Questions

Can Excel automatically insert a row every 5 rows?
Yes. Select the data body and run the macro above, entering 5. It inserts a worksheet row between each group of five records and does not add a gap after the last record.

Does the macro count the header row?
It counts every row in the selected range. Leave the header out of your selection if the interval should apply only to data records.

Can I insert a blank row after every row?
Yes. Enter 1 as the interval. The macro adds a row between each pair of selected records, with no extra row after the final record.

Can I undo the macro with Ctrl+Z?
No. Excel clears its ordinary undo history when a VBA macro changes the workbook. Save a copy first, then restore that copy if you need to reverse the operation.

Will it work in Excel for the web?
No, Excel for the web cannot run VBA. Insert the rows manually in the browser, or open the workbook in desktop Excel to run the macro.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.