Insert a Blank Row after Every Row in Excel (or Every Nth Row)

In this tutorial, I will show you some really simple ways to insert a blank row after every row in an existing dataset (or after every Nth row).

People who work with large data sets often need simple things such as inserting or deleting rows and columns.

While there are already many different (and simple) ways to add rows in Excel, when it comes to inserting a blank row after every other row (or every third or fourth row), things get a bit complicated.

Since there is no direct way to add a blank row in between every row in one go, the sort method in this article is a workaround to make this happen. And if you’re comfortable with VBA, you can do this with a single click.

Insert a Blank Row Between Each Row Using Sort

Let’s start with a method that needs no formulas or code, just a helper column and Excel’s Sort feature. (If you only need a blank row in a few places, it’s quicker to insert those rows manually.)

Below is a list of pet grooming bookings on the Every Row sheet, and I want to insert a blank row after each booking.

Here’s the idea. You number the bookings, put the same numbers in the empty rows below the data, and then sort, so each empty row slides in right below the booking with the same number.

Below are the steps to insert blank rows between existing rows:

1. Insert a blank column to the left of the dataset. To do this, right-click on the column header of the left-most column (column A) and click on Insert.

2. Enter the text ‘HelperColumn’ in cell A1 (you can use any text you want).

3. Enter 1 in cell A2 and 2 in cell A3.

4. Select both the cells and place the cursor at the bottom-right of the selection. When the cursor changes to a plus icon (the fill handle), double-click on it. This will fill the column with incrementing numbers, from 1 in A2 to 10 in A11.

5. Go to the cell below the last filled cell in the helper column (A12). Enter 1 in this cell and 2 in the cell below it (A13).

6. Select both the cells and place the cursor at the bottom-right of the selection. When the cursor changes to a plus icon, click and drag it down to cell A21, so you get the numbers 1 to 10 again.

Make sure you get at least as many numbers as there are records in the dataset. We have 10 bookings here, so this second series needs to go up to at least 10.

If it stops short, say at 8, the last two bookings won’t get a blank row after them.

You can also get the same numbers by copying A2:A11 and pasting it in cell A12.

7. Select the entire dataset, including all the cells in the helper column (A1:E21). A quick way to do this is to select any cell in the data and press Control + A.

8. Click the Data tab, and then click on the Sort option (in the Sort & Filter group).

9. In the Sort dialog box, make sure the ‘My data has headers’ option is checked, and then use these settings: Sort by HelperColumn, Sort On Cell Values, and Order Smallest to Largest.

If ‘My data has headers’ is unchecked, Excel treats the HelperColumn text in A1 as data and moves your header row to the bottom of the list.

10. Click OK.

You would notice that as soon as you click OK in the Sort dialog box, it instantly rearranges the rows, and each blank row moves up right below the booking with the same number.

11. Delete the helper column. To do this, right-click on the column A header and click on Delete.

Now you have a blank row after every row of your dataset.

In reality, this is not really inserting a blank row. This sorting method is simply rearranging the data by placing blank rows from below the dataset in between the rows in the dataset.

This works because each booking and one blank row share a number in the helper column (both have a 1, both have a 2, and so on).

When two rows have the same number, Excel keeps them in their original order. So every booking stays on top and its blank row lands right below it.

What if you want two blank rows after every row?

Fill the 1 to 10 series below the data twice instead of once (A12:A21 and then A22:A31), select A1:E31, and then sort the same way. Each extra copy of the series gives you one more blank row after every booking.

Insert a Blank Row After Every Nth Row

You can also extend the same logic to insert a blank row after every two rows or every three rows. The only thing that changes is the numbers you put below the dataset.

Below is the same list of pet grooming bookings on the Every Nth Row sheet, and I want to insert a blank row after every two bookings.

Below are the steps to do this:

1. Insert a blank column to the left of the dataset. To do this, right-click on the column header of the left-most column (column A) and click on Insert.

2. Enter the text ‘HelperColumn’ in cell A1 (you can use any text you want).

3. Enter 1 in cell A2 and 2 in cell A3.

4. Select both the cells and place the cursor at the bottom-right of the selection. When the cursor changes to a plus icon, double-click on it. This will fill the column with incrementing numbers, from 1 in A2 to 10 in A11.

5. Go to the cell below the last filled cell in the helper column (A12). Enter 2 in this cell and 4 in the cell below it (A13). We are using numbers in multiples of 2 as we want one blank row after every two rows.

6. Select both the cells and place the cursor at the bottom-right of the selection. When the cursor changes to a plus icon, click and drag it down to cell A16, so you get 2, 4, 6, 8, and 10.

Make sure this series reaches at least the number of records in your dataset. We have 10 bookings, so it needs to go at least till the number 10.

7. Select the entire dataset, including all the cells in the helper column (A1:E16).

8. Click the Data tab, and then click on the Sort option.

9. In the Sort dialog box, make sure ‘My data has headers’ is checked, and then use these settings: Sort by HelperColumn, Sort On Cell Values, and Order Smallest to Largest.

10. Click OK.

11. Delete the helper column.

This gives you the final data set with a blank row after every second booking.

The blank rows carry the numbers 2, 4, 6, 8, and 10. So when Excel sorts, each one lands right after the booking with the same number, which is after every second booking.

Similarly, in case you want to insert a blank row after every third row, use the numbers 3, 6, 9, and so on in step 5.

Insert Alternate Blank Rows Using VBA

While you need a lot of workarounds to insert alternate blank rows in Excel, with VBA it’s all a piece of cake.

With a simple VBA code, all you need to do is select the dataset in which you want to insert a blank row after every row, and simply run the code (takes a single click).

Below is the VBA code that will insert a blank row after every row in the dataset:

Sub InsertAlternateRows()
'This code will insert a blank row after every row in the selection
'This code has been created by Sumit Bansal from trumpexcel.com
Dim rng As Range
Dim i As Long
Set rng = Selection
For i = rng.Rows.Count - 1 To 1 Step -1
    rng.Rows(i).Offset(1, 0).EntireRow.Insert
Next i
End Sub

Sub InsertAlternateRows()
'This code will insert a blank row after every row in the selection
'This code has been created by Sumit Bansal from trumpexcel.com
Dim rng As Range
Dim i As Long
Set rng = Selection
For i = rng.Rows.Count - 1 To 1 Step -1
    rng.Rows(i).Offset(1, 0).EntireRow.Insert
Next i
End Sub

The above code counts the total number of rows in the selection and uses a For Next loop to go through them from the bottom up, inserting a blank row below each one.

Working from the bottom up means a newly inserted row never pushes down a row the code still has to process. And since the loop starts at the second-last row, you don’t get an extra blank row after the last record.

Here are the steps to place this VBA code in the VB Editor in Excel:

1. Copy the above code.

2. Go to the Developer tab and click on the Visual Basic option. This will open the VB Editor. You can also use the keyboard shortcut ALT + F11.

3. In the VB Editor, right-click on any object in the Project Explorer, hover the cursor over the Insert option, and then click on Module. This will insert a new module.

4. In the Module code window, paste the above code.

Once you have the code in the VB Editor, you can use it to insert blank rows after every row in your dataset.

Below is the same list of pet grooming bookings on the VBA Macro sheet, and I want to insert a blank row after each booking.

Important: You can’t undo what a macro does with Control + Z. So save your workbook (or keep a copy of the data) before you run the code.

Here are the steps to use the code to insert blank rows after every row:

1. Select the entire dataset except the header row (A2:D11).

2. Click the Developer tab (in case you don’t have the Developer tab, click here to learn how to get it), and then click on the Macros option.

3. In the Macro dialog box, select the macro ‘InsertAlternateRows’.

4. Click on Run.

That’s it!

The above steps would instantly insert a blank row after every row in the dataset.

Pro Tip: There are many different ways to run a macro in Excel. If you have to do this quite often, you can add this macro to the Quick Access Toolbar so that you can run it with a single click.

You can read more about different ways to run macros here.

If you want to keep the macro in your workbook, save it as an Excel Macro-Enabled Workbook (.xlsm), because a regular .xlsx file drops the code when you save it. Also, macros don’t run in Excel for the web, so use the sort method there.

In case you want to insert a blank row after every second row (or every third or fourth row), you can use the below code:

Sub InsertBlankRowAfterEveryNthRow()
'This code will insert blank rows after every Nth row in the selection
'This code has been created by Sumit Bansal from trumpexcel.com
Dim rng As Range
Dim i As Long
Dim n As Long
Dim BlankRows As Long
n = 2 'insert blank rows after every 2 rows
BlankRows = 1 'number of blank rows to insert each time
Set rng = Selection
For i = rng.Rows.Count - 1 To 1 Step -1
    If i Mod n = 0 Then rng.Rows(i).Offset(1, 0).EntireRow.Resize(BlankRows).Insert
Next i
End Sub

Sub InsertBlankRowAfterEveryNthRow()
'This code will insert blank rows after every Nth row in the selection
'This code has been created by Sumit Bansal from trumpexcel.com
Dim rng As Range
Dim i As Long
Dim n As Long
Dim BlankRows As Long
n = 2 'insert blank rows after every 2 rows
BlankRows = 1 'number of blank rows to insert each time
Set rng = Selection
For i = rng.Rows.Count - 1 To 1 Step -1
    If i Mod n = 0 Then rng.Rows(i).Offset(1, 0).EntireRow.Resize(BlankRows).Insert
Next i
End Sub

The n line decides how many rows sit between the blank rows, and the BlankRows line decides how many blank rows go in each time. For example, n = 3 and BlankRows = 2 gives you two blank rows after every third row.

And if you set n to 1 and BlankRows to 2, you get two blank rows after every row. You run this macro the same way, by selecting the data without the header and running ‘InsertBlankRowAfterEveryNthRow’.

So these are two ways you can use to insert a blank row after every row (or every Nth row) in Excel, one with a helper column and Sort, and one with a simple VBA code.

Hope you found this tutorial useful.

You may also like the following Excel tutorials:

• How to Delete Every Other Row in Excel (or Every Nth Row)
• Highlight EVERY Other ROW in Excel (using Conditional Formatting)
• How to Select Every Third Row in Excel (or select every Nth Row)
• Delete Blank Rows in Excel (with and without VBA)
• How to Quickly Select Blank Cells in Excel
• Hide Zero Values in Excel | Make Cells Blank If the Value is 0
• Insert New Columns in Excel
• Select Every Other Row in Excel
• How to Delete Entire Row in Excel Using VBA (Examples)