Excel Insert Lines Between Each Row: Simple Methods

How-to basics · Updated November 19, 2026

To insert actual blank rows between records in Excel for Microsoft 365 on Windows, start at the bottom of your data and work upward: select the row header below a record, then choose Home > Insert > Insert Sheet Rows. Repeat between each pair of records, stopping before the final record. For many rows, a short VBA macro can do this automatically; if you only need a spaced-out view, use a border instead of inserting rows.

What “lines” means in Excel

People use “lines between rows” to mean two different things. They may want empty worksheet rows between records, which create room for notes or make a printout easier to read. Or they may mean visible horizontal rules separating records. The first changes the worksheet structure; the second is only formatting.

That difference matters. Inserting blank rows shifts cells below the insertion point and can affect formulas, charts, print layout, and other references. Adding borders changes the appearance without changing where the data is stored. If you are unsure, try borders first: they are easier to remove and do not create gaps that sorting or filtering must account for.

Insert blank rows manually for a short list

For a small list, inserting rows one at a time is straightforward. Assume row 1 contains column headings and your records occupy rows 2 through 6. Insert a new sheet row at row 6, then at row 5, then row 4, and finally row 3. Working upward preserves the original row numbers above each insertion, so each new row lands between a pair of records. Do not insert below the final record unless you also want a trailing blank row.

  1. Click the row number immediately below the first gap you want to create. Selecting the row header selects the whole worksheet row.
  2. Choose Home > Insert > Insert Sheet Rows. Excel adds a blank row above the selected row.
  3. Continue with the next desired gap, moving upward through the worksheet.
  4. Check that the headings remain at the top and that there is no extra blank row after the last record.

On Windows, the keyboard shortcut for inserting a sheet row after selecting its row header is Ctrl+Shift+plus (+). On many keyboards, the plus sign is entered with Shift and the equals key, so the keystroke is shown as Ctrl+Shift+=. Excel for Mac also supports an insert-row shortcut, but keyboard layouts and shortcut behavior can differ; use the Home tab’s Insert menu if the shortcut does not work on your Mac. The ribbon command is also a reliable option in Excel for the web, where desktop shortcuts can vary by browser.

Choose a method based on the job

The right approach depends on whether the blank rows must exist in the original data, how many records you have, and whether you need to repeat the task. For a handful of records, manual insertion is simple and easy to inspect. For a large dataset, repeated clicking is slow and makes it easier to miss a gap. A formula-built layout is useful when you want a separate display that updates from source data. A macro is useful when you need actual inserted rows and can run VBA.

  • Small, one-time change: insert rows manually from bottom to top.
  • Readable report or copy: create an interleaved formula output in an unused area or another sheet.
  • Large list that needs real blank rows: use a macro, after saving a copy and checking the data layout.
  • Only visual separation: apply bottom borders or a table style instead of adding rows.

Create a spaced-out copy with a formula

A formula can produce a separate display with a blank-looking row after each source record. This leaves the original data unchanged, which is useful when other calculations depend on a compact, uninterrupted table. For example, suppose headings are in A1:D1 and ten records are in A2:D11. Copy the headings to F1:I1. In F2, enter the formula below, then fill it right through I2 and down through row 20. That creates 19 output rows: ten data rows alternating with nine blank-looking rows.

With source data in A2:D11 and the output starting in F2, enter this formula in F2, then fill across to I2 and down to I20:

=IF(MOD(ROWS($F$2:F2),2)=1,INDEX($A$2:$D$11,ROUNDUP(ROWS($F$2:F2)/2,0),COLUMNS($F:F)),"")

In the output, F2:I2 shows the first source record, F3:I3 appears blank, F4:I4 shows the second record, and so on. The formula uses standard Excel functions and can be copied across and down; it does not require a newer dynamic-array function. If the source has a different number of records or columns, adjust the source range and extend the output to the required number of rows and columns.

There is an important limitation: cells displaying an empty string from a formula are not truly empty cells. Some functions, data-entry checks, or later processing may treat them differently from untouched cells. This method also creates a separate output, not physical gaps in the source. If you need genuinely empty worksheet rows between the original records, use manual insertion or VBA.

Insert many actual rows with VBA

For a long list, a macro can insert the rows more consistently than doing it by hand. The example below assumes row 1 is a header and every data record has a value in column A. It inserts one blank worksheet row between data rows, but not after the last record. Change firstDataRow if your records start elsewhere, and change the column used in the lastDataRow statement if another column is more consistently filled.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and inserting worksheet rows affects the active sheet.

Sub InsertBlankRowsBetweenDataRows()
    Dim ws As Worksheet
    Dim firstDataRow As Long
    Dim lastDataRow As Long
    Dim r As Long

    Set ws = ActiveSheet
    firstDataRow = 2 ' Row 1 contains headings
    lastDataRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastDataRow <= firstDataRow Then Exit Sub

    ' Insert from the bottom upward to preserve row positions.
    For r = lastDataRow - 1 To firstDataRow Step -1
        ws.Rows(r + 1).Insert Shift:=xlDown
    Next r
End Sub

To use it in desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code into the module, and run the macro while the intended worksheet is active. If Excel displays a macro security notice, only enable macros for workbooks and code you trust. Excel for the web cannot create or run VBA macros; open the workbook in the desktop app to use this method. On Mac, the Visual Basic Editor and its shortcuts differ, but VBA can be used in desktop Excel for Mac.

The example identifies the last record using column A. If that column contains gaps, Excel may stop at an earlier row and leave later records untouched. Choose a column that is filled for every record, or revise the last-row logic for your sheet. Also review the result before using it in a workbook with merged cells, filtered data, or complex layouts: inserted sheet rows can affect neighboring content beyond the visible data block.

Use borders when you mean visible lines

If the goal is simply to make rows easier to follow, borders usually preserve a cleaner data structure. Select the cells in the data area, then choose Home > Borders and pick a bottom-border option. A bottom border on each record creates horizontal separators without adding worksheet rows. You can also format the range as a table using Home > Format as Table; table styles can use banded rows, and tables provide built-in filtering and consistent formatting.

For printed sheets, check the result in Print Preview before inserting blank rows just to increase spacing. Blank rows consume page space and may push records onto additional pages. Adjusting row height, borders, table styling, or page scaling may produce a more readable printout while keeping the underlying records together.

Common problems and checks

Inserted rows can change more than the visual spacing. Excel adjusts many formulas when rows are inserted, but references in other sheets, external links, charts, named ranges, and formulas with fixed text-based addresses deserve a check. If the range is an Excel table, inserting a worksheet row near or inside it may expand the table or alter its layout. Consider whether filters, totals, and structured references still behave as intended.

  • Rows appear in the wrong place: when inserting manually, work from bottom to top. Inserting from top to bottom changes the positions of records below each insertion.
  • Only some data moved: select the row number to insert a whole worksheet row. Selecting only cells may open options to shift cells, which can misalign columns.
  • The macro stops too early: check whether the chosen last-row column contains blanks within the data. Use a reliably populated column for the final-row calculation.
  • Sorting or filtering becomes awkward: blank rows can divide a contiguous list into separate regions. Remove the gaps before sorting or filtering if Excel does not recognize the full range as one block.
  • The formula output is not accepted as empty: formula-generated empty strings look blank but are not the same as cells with no formula. Use real inserted rows where true blanks are required.

Frequently Asked Questions

Can Excel add a blank row between every row automatically?
Yes. For a large list, a VBA macro can insert actual rows between records. For a small list, insert rows manually from the bottom upward. A formula can make a spaced-out copy, but does not add rows to the original data.

How do I insert a row between two rows without moving data out of alignment?
Select the row number below the intended gap and choose Home > Insert > Insert Sheet Rows. This inserts a whole worksheet row. Avoid selecting only one cell unless you specifically want to shift part of the worksheet.

Can I put a line between rows without adding blank rows?
Yes. Select the relevant cells and apply a bottom border from Home > Borders, or use a table style with banded rows. These options change appearance without changing the data layout.

Will blank rows affect sorting and filtering?
They can. Blank rows may break a continuous data range, so sorting or filtering might apply to only one section. Keep records contiguous when you regularly sort or filter, or select the entire intended range before using those tools.

Can I use this method in Excel for the web?
You can insert rows from the ribbon and use worksheet formulas in Excel for the web. The VBA macro requires desktop Excel; the web version does not run VBA.

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.