Yes. In Excel for Microsoft 365 on Windows, you can insert a blank row between existing rows by adding rows one at a time from the bottom upward, using a short VBA macro, or generating a separated copy with a formula. For a few rows, the manual method is straightforward; for a large list, a macro or formula is faster.
What “every other row” means
Usually, this request means placing one empty worksheet row between each pair of existing data rows. If your list occupies rows 2 through 6, the result has a blank row between rows 2 and 3, 3 and 4, 4 and 5, and 5 and 6. That creates four blank rows, not five: there is no blank row after the last record unless you specifically want one.
Be clear about whether the heading row is part of the range. Most lists have a header in row 1 and records beginning in row 2. Inserting between the records should leave the heading untouched. The methods below distinguish between changing the original worksheet and making a separated copy elsewhere.
Insert rows manually for a short list
For just a few gaps, insert whole worksheet rows in reverse order. Starting at the bottom prevents each new row from changing the position of the next gap you plan to make.
- Find the bottom pair of records that need a blank row between them.
- Select the row number for the lower record. For example, to make a gap between rows 5 and 6, select row 6.
- Choose Home > Insert > Insert Sheet Rows, or right-click the selected row number and choose Insert.
- Repeat for the other gaps, moving upward through the sheet.
For example, if records are in rows 2 through 6, insert above row 6, then above row 5, then above row 4, and finally above row 3. The result places a blank between each original record. If you start at the top instead, inserted rows push later records downward, so the row numbers for the remaining gaps change.
On Windows, with a row selected, Ctrl+Shift++ is the keyboard shortcut for inserting rows; on many keyboards the plus sign requires Shift and the equals key. On Mac, the corresponding shortcut is Control+Shift+=. If the shortcut behaves differently on your keyboard or Excel version, use the ribbon or row-header context menu.
Use a formula to create a separated copy
A formula is useful when you want to preserve the original list and show an interleaved result in another part of the sheet. In an empty area, enter a dynamic-array formula that returns the original rows with a blank output row between them. This example assumes the records, without a header, are in A2:C5:
=MAKEARRAY(ROWS(A2:C5)*2-1,COLUMNS(A2:C5),LAMBDA(r,c,IF(MOD(r,2)=1,INDEX(A2:C5,(r+1)/2,c),"")))
The formula returns seven rows and three columns: four rows of source data with three blank-looking rows between them. The blank outputs are empty text values, not truly empty worksheet cells. MAKEARRAY and LAMBDA are available in Microsoft 365 and Excel 2024; this formula is not suitable for older Excel versions that lack those functions. Keep the spill area clear, or Excel will show a #SPILL! error. If you need to replace the original data, copy the result and use Paste Special > Values in the destination before removing the source.
To adapt the example, change A2:C5 to the actual contiguous data range. Do not include the header unless you also want it treated as a record. The formula creates a displayed result; it does not physically insert rows into the source range, and it does not copy the source formatting.
Insert many rows with a VBA macro
For a long list that needs actual blank worksheet rows, a macro can insert the gaps in one pass. First select a single, continuous range of data rows, excluding the header. The code uses the selected range’s first and last worksheet rows, then works upward so inserted rows do not disrupt the remaining positions.
Sub InsertBlankRowsBetweenSelectedRows()
Dim firstDataRow As Long
Dim lastDataRow As Long
Dim r As Long
' Select one contiguous range of data rows; exclude the header.
firstDataRow = Selection.Row
lastDataRow = Selection.Row + Selection.Rows.Count - 1
' Insert a full worksheet row between each selected row.
For r = lastDataRow - 1 To firstDataRow Step -1
Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next r
End Sub
To run it, save a copy of the workbook first. Press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, paste the code, and run the macro while the intended worksheet and selection are active. On Mac, the Visual Basic Editor can be opened from the Developer tab or Tools > Macro > Visual Basic Editor, depending on the Excel setup; the editor and macro code are similar. You may need to save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
Macros cannot be undone with Ctrl+Z, so the backup matters. This macro inserts entire worksheet rows, not just cells in the selected columns. It is designed for a contiguous selection on one worksheet; do not run it with a multi-area selection or when you intend to alter only part of a table. Excel Tables have their own structured behavior, so check the result carefully if the selected records belong to a table.
Choose a method based on the result you need
- A few gaps in the original list: Insert whole rows manually, beginning at the bottom.
- Many gaps in the original worksheet: Use the macro after saving a separate copy.
- A non-destructive preview or report: Use the formula in a clear area and keep the source data intact.
- A repeatable process for changing data: Consider a formula-based output that recalculates when the source range changes, or document a macro carefully for future use.
Also consider why you need the blank rows. For visual spacing, alternating row shading or table banding may be easier to maintain and safer for sorting and filtering. If you intend to add notes or calculations between records, physical blank rows may make some data operations less convenient.
Formatting, formulas, and Excel Tables
When you insert a sheet row, Excel may carry formatting from a neighboring row. The manual Insert Options control or the macro’s CopyOrigin setting affects how inserted rows inherit formatting. Inspect borders, number formats, fills, and conditional formatting after insertion, especially when the list has different formatting at its top and bottom.
Formulas that refer to cells can adjust when rows are inserted, but do not assume every workbook feature will behave exactly as intended. Check totals, charts, named ranges, and formulas that use fixed row numbers. A blank row inside a contiguous data range can also affect operations that expect uninterrupted records, including sorting, filtering, and some chart ranges.
If the records are formatted as an Excel Table, adding empty worksheet rows between records is usually not a good way to create visual separation. A table is designed as a continuous data structure; blank rows can interrupt the way users filter, sort, and work with records. Use the table’s built-in banded rows or apply conditional formatting for alternating visual emphasis instead. If you must create physical gaps, make a copy outside the table and verify the resulting layout.
Common problems and how to avoid them
- The gaps appear in the wrong places: When inserting manually, start from the bottom and work upward.
- The header moves or gets a gap beneath it: Exclude the header from the selected data range, or deliberately choose whether a gap below the heading is wanted.
- The formula returns #SPILL!: Clear cells in the destination area so the result can expand into them. Merged cells in that area can also block a spill.
- The formula result looks blank but is not empty: The formula returns an empty text string for separator rows. This is suitable for display, but not identical to inserting genuinely empty rows.
- Sorting or filtering behaves unexpectedly: Blank rows divide a list into separate blocks for some tasks. Remove the gaps before sorting or filtering, or work with a continuous copy.
- The macro changes too much: It inserts full worksheet rows. Confirm that the selected range is correct and save a backup before running it.
Alternatives to inserting blank rows
If your goal is to make records easier to follow, try a visual alternative before changing the data structure. Excel Tables can display banded rows; the Table Design tab includes table style options for alternating row shading. For a regular worksheet range, conditional formatting can apply a fill to alternating rows, keeping the records continuous. This makes sorting, filtering, copying, and using formulas less likely to be disrupted by gaps.
For printing, use page layout and print settings rather than inserting spacer rows solely to control page breaks. For notes, add a dedicated Notes column or keep comments alongside the relevant record. Blank rows are appropriate when the sheet is a presentation layout, but they are often inconvenient in a dataset that will be analyzed or updated.
Frequently Asked Questions
Can Excel insert a blank row after every row automatically?
Yes. For a large selection, the VBA macro above inserts a full worksheet row between selected records. For a small number of gaps, insert rows manually from the bottom upward. The formula method displays a separated copy instead of modifying the source.
How many blank rows are needed between 100 records?
To put one blank row between each neighboring pair, 100 records need 99 blank rows. There is no gap after the final record unless you choose to add one.
Can I insert rows without changing the original data?
Yes. Use the formula in a separate, empty area, or make a copy of the worksheet first. The formula creates a new displayed result and leaves its source range unchanged.
Why should I insert rows from the bottom?
Each inserted row shifts rows beneath it downward. Working upward keeps the positions of the gaps you have not yet inserted stable, which reduces the chance of leaving gaps in the wrong places.
Will blank rows affect sorting and filtering?
They can. Empty rows may divide a continuous list into separate blocks, so sorting or filtering may not include every record as expected. Keep analytical data contiguous and use banded formatting when you only need visual separation.
Related
- Remove a Range Name in Excel: Step-by-Step Guide
- How to Use Absolute References in Excel for Mac
- How to Name a Print Range in Excel and Reuse It for Printing
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Inventory Spreadsheet Template for Google SheetsFor an inventory spreadsheet, use Google Sheets rather than Google Docs: Sheets is Google’s spreadsheet app, while Docs…
- How to Export Outlook Suggested Contacts to CSVOutlook does not provide a single command to export its autocomplete suggestions. To get them into a CSV…
- Excel Data Validation Methods: Rules, Lists, and TipsExcel data validation methods control what users can enter in a cell: choose a built-in rule for numbers,…
- How to Schedule a Meeting in Outlook Email: Step-by-StepTo schedule a meeting from an Outlook email, create a calendar meeting, add the people you want to…
- Excel File Format or File Extension Not Valid? FixesIf Excel says the file format or file extension is not valid, it usually means the workbook’s contents…
- Excel Power Query: Import Multiple CSV FilesTo import and combine multiple CSV files in Excel for Microsoft 365 on Windows, put the files in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.