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 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
Select the first row by clicking on its row number.
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:
Now, a blank row will be inserted before each of the selected rows. See screenshot:
While this method is suitable for small datasets in your worksheet, it may become time-consuming and inefficient when dealing with larger amounts of data. In such cases, the following methods can be used to efficiently insert a blank row every other or nth row in a large dataset.
When selecting the first row of data, make sure not to hold down the Ctrl key. Otherwise, a warning message will be displayed when using the Insert feature.
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
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:
Then, enter 1 and 2 in cell E2 and E3 separately.
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:
Now, copy the sequence numbers and paste them just below the last cell E10. See screenshot:
If you want to insert a blank row after every two rows, in this example, you should enter 2 and 4 in cells E11 and E12 individually. Then, select these two cells and drag the fill handle down to fill a series of numbers. Make sure the last number in the series is equal to or greater than the highest number in your dataset.
Similarly, to insert a blank row after every three rows, please enter 3 and 6 in cell E11 and E12 individually. And so on…
If you want to insert two or more blank rows between existing rows, you just need to copy and paste the helper column list two or more times below the last cell.
Step 2: Apply the Sort feature to sort the data by helper column
Now, select the data in the Helper column, and then click Data > Sort, see screenshot:
In the popped-out Sort Warning dialog, please select Expand the selection option, and then click Sort button, see screenshot:
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:
Now, the data will be sorted based on the values in the helper column, and a blank row is inserted after every other row.
Note: You can delete the helper column if needed.
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.
Select the data range where you want to insert blank rows.
Choose Blank Rows from the Insert type section.
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.)
At last, click OK to close the dialog.
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
Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
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
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
xNum1 = xNum1 + xNum2
Step 2: Execute the code to get the result
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:
Another prompt box will pop out, please enter the number of row interval. And then, click OK, see screenshot:
In the following prompt box, please enter the number of blank rows that you want to insert. Then, click OK, see screenshot:
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, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!
When you need to quickly insert 10 blank rows between Row 2 and Row 3 or insert 10 blank rows above specific row in Excel, how would you do? Usually you may insert each blank row one by one. This article focuses on tricky ways to insert multiple blank rows or columns quickly in Excel.
In your daily work, have you ever tried to copy a row or each row and then insert multiple times below the current data row in a worksheet? For example, I have a range of cells, now, I want to copy each row and paste them 3 times to the next row as following screenshot shown. How could you deal with this job in Excel?
Supposing you have a range of data, and now you want to insert blank rows between the data when value changes, so that you can separate the sequential same values in one column as following screenshots shown. In this article, I will talk about some tricks for you to solve this problem.
When you are working with large datasets that contain blank rows, it can clutter your worksheet and hinder data analysis. While you can manually remove a small number of blank rows, it becomes time-consuming and inefficient when dealing with hundreds of blank rows. In this tutorial, we present six different methods to efficiently remove blank rows in batches. These techniques cover various scenarios you may encounter in Excel, allowing you to work with cleaner and more structured data.