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.
- Click the row number immediately below the record that needs a blank row.
- On the ribbon, choose Home > Insert > Insert Sheet Rows. Excel inserts a whole worksheet row above the selected row.
- 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
- Excel Hide Columns From Certain Users: What Actually Works
- Excel Hide Column Letters? What the Setting Actually Does
- Excel: How to Hide Column Lines and Gridlines on a Worksheet
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add Page Numbers in Word Using a Keyboard ShortcutIn Word for Microsoft 365 on Windows, press Alt+N to open the Insert tab, then follow the on-screen…
- IFS Formula in Google Sheets: How to Use ItUse the IFS function in Google Sheets to test conditions in order and return the value for the…
- How to Insert a Line in Word: Borders and Shapes GuideTo insert a line in a Word document, type three hyphens (—) on a blank line and press…
- Excel Time Value: How Excel Stores and Calculates TimeIn Excel, a time value is a number representing a fraction of a 24-hour day: for example, 6:00…
- Outlook Not Letting You Create Rules? Causes and FixesIf Outlook will not let you create a rule, first check which Outlook version and account you are…
- Make One Page Landscape in Word (Keep Others Portrait)To make just one page landscape in Microsoft Word for Microsoft 365 on Windows, place it in its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.