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.
- Save a copy of the workbook first. Macro changes cannot be undone with Ctrl+Z.
- 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.
- Press Alt+F11 to open the Visual Basic Editor. If the Developer tab is available, you can also choose Developer > Visual Basic.
- In the editor, choose Insert > Module, then paste the code below into the module.
- 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
- Excel Goal Seek Algorithm: How It Finds a Solution
- Does Excel Goal Seek Automatically Update?
- Excel Get Slicer Value in a Cell: Practical Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table Headers on Each Printed Page: How to Repeat ThemTo print the same column headings on every page in Excel for Microsoft 365 on Windows, open Page…
- Excel Keyboard Shortcuts to Delete a RowIn Excel for Microsoft 365 on Windows, press Shift+Space to select the current row, then press Ctrl+- (Ctrl…
- Outlook Distribution Lists: How to Create and Manage ThemYes. You can create a personal distribution list in Outlook—called a contact list in new Outlook and Outlook…
- Excel Dropdown List Without Blanks: Keep Options CleanYes. In Excel for Microsoft 365, use a FILTER formula to create a helper list containing only nonblank…
- Excel Max Value in a Column: Find the Largest NumberIf your values are in B2:B100, enter =MAX(B2:B100) in an empty cell; Excel returns the largest numeric value…
- How to Change Short Date Format in Excel for DatesTo change how dates appear in Excel for Microsoft 365 on Windows, select the date cells, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.