Insert blank row every other row in Excel – 4 Ways

In Excel, inserting a blank row is an easy task for most users. However, there are instances when you may need to insert a blank row between each existing row in your table as the screenshot shown below. This can help to improve readability and make the data easier to navigate. In this article, we will talk about some quick and handy tricks for inserting blank row after every other or nth row in Excel.

Insert blank row every other or nth row in Excel

• By the Insert feature
• By a helper column and the Sort feature
• By Kutools for Excel - with only a few clicks
• By VBA code

Video: Insert blank row every other row in Excel

Insert blank row every other row with the Insert feature

If you have a small amount of data, you can manually select each row by pressing the Ctrl key, and then use the Insert feature to insert a blank row before each existing row. Here is how you can do it:

Step 1: Select the rows manually one by one while golding down the Ctrl key

1. Select the first row by clicking on its row number.
2. Next, press and hold the Ctrl key on your keyboard, and then click on the row numbers one by one that you want to select.
   

Step 2: Select the Insert option from right-click menu

Once you have selected all the rows where you want to insert a blank row, right-click on one of the selected row numbers, and select "Insert" from the context menu, see screenshot:

Result:

Now, a blank row will be inserted before each of the selected rows. See screenshot:

Insert blank row every other or nth row with helper column and Sort feature

The above method is suitable for small data in your worksheet. However, if you’re dealing with a large amount of data, it's better for you to create a helper column and then apply the Sort feature to insert blank row every other or nth row in Excel. Follow these steps to achieve this:

Step 1：Create a helper column

1. Create a helper column adjacent to your data. For example, I have a range of data A1:D10, I will enter the text “Helper column” in cell E1, see screenshot:
   
2. Then, enter 1 and 2 in cell E2 and E3 separately.
3. Select cells E2 and E3, and then drag the fill handle down to the last data cell, a list of incrementing numbers will be filled as below screenshot shown:
   
4. Now, copy the sequence numbers and paste them just below the last cell E10. See screenshot:
   

Step 2: Apply the Sort feature to sort the data by helper column

1. Now, select the data in the Helper column, and then click "Data" > "Sort", see screenshot:
   
2. In the popped-out "Sort Warning" dialog, please select "Expand the selection" option, and then click "Sort" button, see screenshot:
   
3. In the following "Sort" dialog box, select "Helper column" from the "Sort by" drop down list as the key column, and leave the other options unchanged. Then, click OK to close the dialog. See screenshot:
   

Result:

Now, the data will be sorted based on the values in the helper column, and a blank row is inserted after every other row.

Insert blank row every other or nth row with only a few clicks

Sometimes, when you want to insert a specific number of blank rows at regular intervals within your data range, like inserting one or more blank rows after every two or more data rows, the previous method can be complex and hard to grasp. To solve this job quickly and easily, Kutools for Excel’s "Insert Blank Rows & Columns" feature can help you to deal with this job with only several clicks.

After downloading and installing Kutools for Excel, click "Kutools" > "Insert" > "Insert Blank Rows & Columns" to open the "Insert Blank Rows & Columns" dialog box.

1. Select the data range where you want to insert blank rows.
2. Choose "Blank Rows" from the "Insert type" section.
3. Then, specify the numbers into the "Interval of" and "Rows" textboxes as you need. (Here, I will insert one blank row after two rows, so, I enter 2 and 1 into the "Interval of" and "Rows" boxes separately.)
4. At last, click OK to close the dialog.
   

Result:

Now, a single blank row will be inserted into the data after every two rows, see screenshot:

Insert blank row every other or nth row with VBA code

If you are interested in VBA code, here, we will provide a code for inserting specific number of blank rows into your data at fixed intervals. Please do with the following steps:

Step 1：Open the VBA module editor and copy the code

1. Hold down the ALT + F11 keys in Excel, and it opens the "Microsoft Visual Basic for Applications" window.
2. Click "Insert" > "Module", and paste the following code in the Module Window.
   VBA code: Insert specific number of blank rows into data at fixed intervals

   Sub InsertRowsAtIntervals()
   'Updateby Extendoffice
   Dim Rng As Range
   Dim xInterval As Integer
   Dim xRows As Integer
   Dim xRowsCount As Integer
   Dim xNum1 As Integer
   Dim xNum2 As Integer
   Dim WorkRng As Range
   Dim xWs As Worksheet
   xTitleId = "KutoolsforExcel"
   Set WorkRng = Application.Selection
   Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
   xRowsCount = WorkRng.Rows.Count
   xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
   xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
   xNum1 = WorkRng.Row + xInterval
   xNum2 = xRows + xInterval
   Set xWs = WorkRng.Parent
   For i = 1 To Int(xRowsCount / xInterval)
       xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
       Application.Selection.EntireRow.Insert
       xNum1 = xNum1 + xNum2
   Next
   End Sub

Step 2: Execute the code to get the result

1. After pasting this code, please press F5 key to run this code. In the prompt box, select the data range where you want to insert blank rows. Then, click OK,  see screenshot:
   
2. Another prompt box will pop out, please enter the number of row interval. And then, click OK, see screenshot:
   
3. In the following prompt box, please enter the number of blank rows that you want to insert. Then, click OK, see screenshot:
   

Result:

Now, the specific number of blank rows are inserted into the existing data at the regular interval you specified, see screenshot:

This is how you can insert every other or nth row in a data range. If you're interested in exploring more Excel tips and tricks, our website offers thousand of tutorials. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Related Articles: