Excel Insert Blank Row Every Other Row: Methods That Work

How-to basics · Updated November 19, 2026

To insert a blank row between every pair of records in Excel, use a short VBA macro for actual worksheet rows, or create a spaced copy with formulas if you want to keep the source data untouched. For a small list, you can also insert rows manually from the bottom upward.

Choose the method that fits your sheet

“Insert a blank row every other row” can mean either changing the worksheet itself or making a version of the data that looks spaced out. That distinction matters: physical blank rows affect sorting, filters, tables, formulas, and other content on the sheet, while a formula-generated copy leaves the original range in place.

  • Use formulas when you want a separate, refreshable-looking output and do not need to edit the blank spaces as ordinary cells.
  • Use VBA when you need real blank worksheet rows between existing records and are comfortable running a macro.
  • Insert rows manually when the list is short and you only need to do this once.

Before changing a large or important worksheet, save a copy. Inserting rows can shift references and disrupt layouts, and a macro’s changes cannot be reversed with Ctrl+Z.

Create a spaced copy with a formula

A formula-based output is the safest no-macro approach because it does not alter the source. For example, suppose the source data is in A2:C6, with one record per row and no header included in that range. Put this formula in E2:

=IF(MOD(ROWS($E$2:E2),2)=1,INDEX(A$2:A$6,ROUNDUP(ROWS($E$2:E2)/2,0)),"")

Copy E2 across to G2, then copy E2:G2 down through row 10. The relative column reference changes A to B and C as you fill across; the locked source row numbers stay fixed. The output in E2:G10 contains the five source records in alternating rows, with an intervening row that displays as blank. The final output row contains the fifth record, so there is no unnecessary blank after it.

In this example, ROWS counts how far the formula has been filled down. MOD identifies odd output positions, and INDEX returns the corresponding source value. ROUNDUP maps output positions 1 and 3 to source row 1, positions 5 and 7 to source row 2, and so on. The formula in an intervening position returns an empty string, displayed as a blank.

These are formula results, not physical empty rows: a cell containing an empty string is not truly empty. You cannot type into those blank-looking output cells without replacing their formulas. The output also does not automatically inherit the source formatting, and its references will not expand if you add records beyond row 6. Adjust the source range and fill the formulas farther down when the list grows. This method uses INDEX, ROUNDUP, MOD, and ROWS, which are available in Excel versions that support standard worksheet formulas.

Insert actual rows with a VBA macro

For real blank rows, a macro can insert them from the bottom upward. The example below assumes row 1 is a header, records begin in row 2, and every record has a value in column A. It inserts a blank worksheet row after each record except the last.

Sub InsertBlankRowsBetweenData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim firstDataRow As Long
    Dim r As Long

    Set ws = ActiveSheet
    firstDataRow = 2
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Work upward so inserted rows do not change rows still to process.
    For r = lastRow - 1 To firstDataRow Step -1
        ws.Rows(r + 1).EntireRow.Insert
    Next r
End Sub

To run it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code into the module, and run the macro. You can also return to Excel and use Alt+F8 to select and run it. On a Mac, use Option+F11 to open the Visual Basic Editor (some keyboards also require Fn) and Option+F8 to open the macro dialog; menu commands are an alternative if a shortcut differs on your keyboard. Excel for the web cannot run VBA macros, so use desktop Excel for this method.

Save a separate copy of the workbook before running the macro. Macro actions cannot be undone with Ctrl+Z, and inserting entire worksheet rows may shift formulas, named ranges, charts, or content elsewhere on the sheet. The code uses column A to find the last record, so change the column letter if another column is the dependable identifier. If column A has gaps, the detected last row may be too early; use a consistently filled key column or change the code to find the correct boundary.

Insert rows manually for a short list

Manual insertion is straightforward when there are only a few records. Work from the bottom of the list upward so each insertion leaves the positions above it unchanged. If records occupy rows 2 through 6, insert a blank row after rows 5, 4, 3, and 2, in that order.

  1. Click the row number immediately below the record that needs a blank row.
  2. On the ribbon, choose Home > Insert > Insert Sheet Rows. Excel inserts a whole worksheet row above the selected row.
  3. Repeat for the next record above, continuing upward until you have inserted a row after every record except the last.

On Windows, Ctrl++ is the keyboard shortcut for inserting cells or rows after selecting a row; depending on the keyboard, the plus sign is entered with Ctrl+Shift+=. On Mac, Command+Shift+= is the common insert shortcut. If the shortcut opens a dialog or behaves differently, use the ribbon command instead. Avoid selecting several separated row numbers and assuming Excel will insert one row after each selection: inserting noncontiguous selections can behave differently from inserting a single continuous block.

What happens to tables, filters, and formulas?

Excel tables are designed for contiguous records, not spacer rows between every record. Adding worksheet rows inside or around a table can change its boundaries, and blank rows can interfere with the table’s normal filtering, sorting, and automatic expansion behavior. If the source is an Excel table, keep the table compact for data entry and analysis. Use a separate formula output or a presentation copy when the spacing is only for appearance.

Blank rows also divide a normal range into separate blocks. Sorting or applying filters to a range with gaps may not include all the records you expect if Excel identifies only one contiguous block. Select the full intended range before sorting, or better, remove spacer rows before analysis. Formulas that refer to cells below an insertion point may adjust automatically, but do not assume every chart, external link, named range, or manually written formula will still refer to the intended data. Check important outputs after inserting rows.

For the same reason, blank rows are usually a poor way to make a report easier to read if people will later sort, filter, import, or summarize it. Consider using table styles, borders, row shading, or increased row height instead. Those options add visual separation without breaking the continuous data range.

Common problems and how to avoid them

  • A blank row appears after the final record. Insert only between records, not after the last one. The macro above starts at the second-to-last source row for this reason.
  • The macro stops too soon. It finds the last record using column A. If that column has blank cells near the bottom, choose a consistently populated column or revise the last-row detection.
  • The formula output shows zeros or errors. Check that the source range in the formula matches your records and that you filled the formula through exactly 2n−1 output rows for n source records. A source range with five records needs nine output rows.
  • The blank-looking formula cells are not editable blanks. They contain an empty-string result. Copy the output and use Paste Special > Values if you need a static copy, but the cells displaying empty strings may still need clearing to become genuinely empty.
  • Sorting separates records from their intended order. Do not sort a spaced range casually. Remove the spacer rows or sort the original continuous data range instead.

Remove the spacer rows later

If you used the formula method, clear the output range or remove its formulas; the source remains unchanged. For physical rows, you can delete the blank rows individually, or use Find & Select > Go To Special > Blanks only when you have carefully selected the relevant range. Selecting blanks across a larger range can also select empty cells inside records, so deleting entire rows indiscriminately may remove data. A backup is especially useful before bulk deletion.

If you need to restore a sheet after a macro, close the workbook without saving only if you are sure there are no other changes to keep. Otherwise, use the saved pre-macro copy or manually repair the affected range. Undo is not available for the macro’s row insertions.

Frequently Asked Questions

Can Excel insert a blank row after every row automatically?
Yes. A VBA macro can insert actual worksheet rows, while a formula can create a separate output with blank-looking rows. Choose based on whether you need to change the original data or only display a spaced copy.

How do I add a blank row between every two rows without VBA?
Use the formula method to build a separate output, or insert rows manually from the bottom upward. Excel does not provide a simple one-click command that inserts a row after every existing record in an arbitrary range.

Will the formula method create truly empty cells?
No. Its blank-looking positions contain formulas that return an empty string. They look blank but are not equivalent to cleared cells, and they cannot be edited as empty output cells without removing the formulas.

Can I put blank rows inside an Excel table?
It is better to keep an Excel table continuous. Spacer rows can interfere with sorting, filtering, and table expansion. Use a separate display range or formatting to make records easier to distinguish.

Does the VBA macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, or use the formula or manual method instead.

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.