Excel: Insert a New Row Every Other Row

Yes. To insert a genuinely blank worksheet row between each record, select the data rows—not the header—and run a short VBA macro; for a small list, insert rows manually from the bottom upward using Home > Insert > Insert Sheet Rows. A formula can also display records with blank lines between them, but it does not add real worksheet rows.

What “every other row” means

Usually, this request means adding one empty row between each existing data row. For example, a list that currently occupies rows 2 through 5 would end up with records in rows 2, 4, 6, and 8, with blank rows 3, 5, and 7. If row 1 contains column headings, it should stay as the header rather than being included in the alternating pattern.

There is a useful distinction between inserting rows and showing blank lines. Inserting worksheet rows shifts cells below the insertion point, and the new rows are available for later entry, formulas, or printing. A formula-generated layout merely displays blanks in a separate output area. Choose based on whether other parts of your workbook need the rows to exist as actual cells.

Fastest method for real rows: use a macro

For a long, continuous list, a small macro is more reliable than inserting dozens of rows by hand. It works from the bottom of the selected range upward, so each insertion does not throw off the row numbers still to be processed. The selection should contain only the data records you want to separate. For instance, if the header is in row 1 and the records are in rows 2–6, select a cell range such as A2:D6.

Sub InsertBlankRowsBetweenSelectedRows()
    Dim ws As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long
    Dim r As Long

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

    Set ws = Selection.Worksheet
    firstRow = Selection.Row
    lastRow = Selection.Row + Selection.Rows.Count - 1

    ' A single selected row has no gap to create.
    If lastRow <= firstRow Then Exit Sub

    Application.ScreenUpdating = False
    For r = lastRow - 1 To firstRow Step -1
        ws.Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next r
    Application.ScreenUpdating = True

    MsgBox "Blank rows inserted between the selected rows."
End Sub

The loop stops before the last selected record because there is no need to add a trailing blank row after it. The macro inserts entire worksheet rows, not just cells in the selected columns. That is important: any unrelated content to the right or left on those rows will also move down. Review the whole sheet before using it.

How to run the macro safely

Save a copy of the workbook first. Excel macros cannot be undone with Ctrl+Z, so a backup is the simplest way to reverse an unwanted result.
In desktop Excel for Microsoft 365 on Windows, select the data range without its header, then open Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
In the Visual Basic Editor, choose Insert > Module, paste the code into the new module, and run the macro from the editor’s Run menu.
Check the inserted rows, the areas below the list, and any formulas or formatting that depend on row positions. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro.

VBA is available in the desktop Excel app, not Excel for the web. On a Mac, use desktop Excel and its Visual Basic Editor; ribbon and editor access can vary slightly with the Excel version and keyboard setup. If you cannot use macros, use the manual method below or create a formula-based display.

Formula option: create a spaced-out display

In Excel for Microsoft 365, a dynamic-array formula can return the original records with an empty-looking line between them. This is useful for a report or a temporary view when you do not want to alter the source list. Put the formula in an empty area with enough room to spill downward and across; do not place it inside the source range.

With the records in A2:D6 and no header in that range, enter this formula in an empty cell:

=LET(data,A2:D6,n,ROWS(data),MAKEARRAY(2*n-1,COLUMNS(data),LAMBDA(r,c,IF(ISEVEN(r),"",INDEX(data,ROUNDUP(r/2,0),c)))))

For those five source rows, the result is a nine-row-by-four-column spill range: five data rows alternating with four rows that display as blank. The even output rows return empty strings, not truly empty cells. The source data remains unchanged, and the result updates when the source values change. MAKEARRAY and LAMBDA are available in Microsoft 365 and Excel 2024; an earlier Excel release may not support this formula. If Excel reports a spill error, clear any content or merged cells in the area where the result needs to appear.

This option is not a way to insert worksheet rows in the original list. It is also less suitable when another process expects genuine empty cells, or when the result must be edited independently. If you need a fixed copy for presentation, copy the spill output and use Paste Special > Values in a separate location, keeping in mind that the displayed blank strings may still differ from truly empty cells.

Manual insertion for a short list

For a handful of records, the built-in Insert command avoids VBA. Work upward from the bottom of the list so inserting a row does not change the positions of the gaps you have not handled yet. For records in rows 2–5, insert a sheet row after row 4, then after row 3, then after row 2. The header in row 1 remains in place, and each original record gets a blank row after it except the final record.

Click the row number immediately below the record that needs a gap. Selecting the row header makes Excel insert a full worksheet row.
Choose Home > Insert > Insert Sheet Rows.
Repeat for the remaining gaps, moving upward through the original list.

Do not select only one cell and assume Excel will add a full row: depending on the command used, it may offer to shift cells instead. The row-header selection and the Insert Sheet Rows command make the intended operation explicit. For a long list, repeating this process is slow and easy to miscount, which is when a macro or a separate formula output is more practical.

Headers, tables, and formatting to check

Keep column headings out of the selected data range unless you intentionally want a blank line after the headings too. Inserting rows inside a formatted Excel Table can also affect how the table behaves: table formulas, filters, banded formatting, and a totals row may not work as expected with blank records inside the table. If the aim is visual spacing, a separate formula output or a report layout may be cleaner than breaking up a table used for filtering and analysis.

The macro asks Excel to copy formatting from the row above into each inserted row. As a result, the new row may look formatted even though its cells contain no values. Manual insertions can also inherit nearby formatting. If you need the gap to look plain, select the inserted rows and use Home > Clear > Clear Formats; that removes formatting but leaves any cell contents untouched. Check borders, conditional formatting, and print layout too, since those can make a visually blank row appear different from the surrounding sheet.

Common problems and how to avoid them

The header gets separated or moved: select only the records, not the heading row. If the data has a title and a separate header, exclude both.
Rows elsewhere on the sheet shift: the macro inserts entire sheet rows. Move unrelated data to another sheet or use a separate output area if that movement would cause trouble.
The macro does nothing: it intentionally exits for a one-row selection. It also requires one continuous selection; select a single rectangular range rather than several separate areas.
The formula shows #SPILL!: clear the cells in its intended output area and unmerge any cells blocking the result.
Formulas or references change: inserting rows can update formulas and named ranges. Review dependent calculations, charts, print areas, and links before treating the revised workbook as final.

A blank row is not always the best way to create visual separation. If the goal is easier reading, table styles, borders, alternating row colors, or extra cell padding may retain a continuous data set that remains easier to sort and filter.

Which method should you choose?

Use the macro when you need actual worksheet rows between many records and are comfortable running VBA on a saved copy. Use manual insertion when the list is short and you want to avoid code. Use the spill formula when you want a live, separate display and can accept that its blank-looking rows are formula results rather than inserted cells. If the data is an Excel Table used for analysis, consider preserving its continuous rows and using formatting for visual separation instead.

Frequently Asked Questions

Can Excel insert a blank row after every row automatically?
Yes. A VBA macro can insert real rows between a selected continuous range, while a dynamic-array formula can display the records with blank-looking rows in a separate area. For a short list, Home > Insert > Insert Sheet Rows works manually.

Should I include the header in the selection?
No, not if you want the headings to stay at the top without a blank row inserted between them and the first record. Select the data rows only. Include a header only when you deliberately want the same spacing pattern applied to it.

Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to use the macro, or use manual row insertion or a supported formula-based display instead.

Can I undo inserted rows with Ctrl+Z?
Do not rely on that for a macro. Running VBA clears Excel’s normal undo history, so save a copy before running the code. Manual insertions can generally be undone immediately, provided you have not performed another action that replaces the undo step.

Does the formula create genuinely empty rows?
No. The formula returns empty strings for the separating lines, so they look blank but are formula results. Use the macro or manual insertion when you need actual worksheet rows, or when downstream work depends on cells being empty.

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.