How to Insert Blank Row After Every Nth Row in Excel (2 Ways)

Sometimes, you may deal with a dynamic dataset, where you want to put rows or columns after some data. There are no direct built-in features in Excel to do it. But there are some alternative ways to insert blank rows after every nth row in Excel. We have also shown a VBA code to perform the operation. It is a swift and automated way. We also covered the manual method to insert blank rows after the nth row in Excel. So, let’s get started to familiarize ourselves with the process of inserting blank rows after every nth row in Excel. So let’s get started.

Insert Blank Row After Every Nth Row in Excel: 2 Methods

Before analyzing the methods, we need to insert a dataset. Here, we have taken a dataset of the Employee Records of Sunflower Kindergarten. Now, we want to insert rows after every nth row.

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.

1. Using Sort and Filter Feature

While inserting blank rows you can use Excel’s built-in Sort and Filter feature. This feature helps you to insert blank rows after the nth row. But you have to insert a Helper column and input additional rows where you have to input the n number. Follow the below steps for better clarification.

📌 Steps:

• Firstly, take a new column and insert 1 and 2 sequentially in the first two cells of the column. I have named the column “Helper Column”.
• Then, drag the Fill Handle to the rest of the cells. It will generate a sequence of the form {1, 2, 3, 4, 5, 6, …}

• Eventually, insert the value of n in the next cell of the column. For this example, we have inserted n as 3. Then insert the double of n in the next cell. In this example, it is 6.
• Consequently, drag down the Fill Handle tool to a few cells downwards.

• Sequentially, select the whole data set (including the “Helper Column” and the extended portion down the end).
• Now, navigate to the Home tab >> choose Sort & Filter from the Editing group >> pick the Custom Sort tool in the Excel toolbar.

• Consequently, after clicking on the Custom Sort, the Sort dialog wizard pops out.
• Moreover, from the Sort by option, select the “Helper Column”. From the Sort on option, pick Cell Values, and from the Order option, select Smallest to Largest.
• Lastly, hit OK.

• After that, you will find blank rows inserted after every nth row (3rd in this example) in your data set.

Finally, delete the “Helper Column” to make your dataset more presentable.

Similar Readings

• How to Insert Multiple Rows After Every Other Row in Excel
• How to Insert Multiple Rows in Excel
  
• How to Insert a Row Within a Cell in Excel

2. Incorporating VBA Macros

The Sort & Filter method described above works, but it is a bit complex. Furthermore, it has a few limitations. You can insert exactly one blank row after each number of rows, but what to do if one blank row is not enough?

You need to insert two, three, or even more blank rows after each number of rows. Then?

The Sort & Filter method won’t give you flexibility in this regard. Moreover, if the number of n becomes large, like 10, 15, or more, the process becomes more complex.

But you can accomplish the same task using a small piece of VBA code. And you will get a lot more flexibility with this method.

Therefore, let’s see how to create and run the macro to insert blank rows with simple steps.

📌 Steps:

• In the very beginning, you need to navigate to the Developer tab >> choose Visual Basic.

• Subsequently, the dialog box appears. Select the Insert tab >> Module >> Module1.

• Now, from Module 1 in the General box write up the following code.

Sub Insert_Blank_Rows()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Dim n As Integer
n = Int(InputBox("Enter the Value of n: "))
k = Int(InputBox("Enter the Number of Blank Rows: "))
Set rng = Selection
CountRow = rng.EntireRow.Count
For i = 1 To Int(CountRow / n)
    For j = 0 To k - 1
        ActiveCell.Offset(n + j, 0).EntireRow.Insert
    Next j
    ActiveCell.Offset(n + k, 0).Select
Next
End Sub

• At this moment, return to your worksheet and select the whole data set (without the Column Headers).

• Run this Macro.
• Eventually, you will get an InputBox asking you to provide the value of n. Enter it. I have entered it as 3. Then click OK.

• However, you will get another InputBox asking you to enter the number of blank rows. I have entered it as 2. This means, after each nth row, a series of 2 blank rows will be created. If you want one blank row, enter 1. Then click OK.

Finally, you will find that after each nth row of your data set, a series of blank rows (2 in this example) have been created.

How to Insert Blank Rows Automatically in Excel

You can add a row before the selected rows in Excel automatically. It is pretty easy to perform the operation, but the problem is that you can only insert one row before the selected row. You can’t insert the nth number of rows there. As it is an automatic process, that’s why we attached it here. To get a better visualization, follow the steps below.

📌 Steps:

• Firstly, select one specific row of your dataset.
• Secondly, hold the CTRL key and click on the other cells to add the rows.

• Eventually, leave the CTRL key and right-click on any selected cell.
• Sequentially, the Context Menu appears. Choose the Insert command.

Apparently, one row before the selected rows has been added automatically.

Read More: How to Insert Rows Automatically in Excel

How to Insert Multiple Blank Rows Between Data in Excel

Moreover, you can insert multiple blank rows in Excel between data. It is a very easy and time-saving task. But you can only insert the number of rows that are selected. For your better understanding, we will explain this fact. Suppose you select two rows; then you can insert only two rows before the selected rows. Follow the below steps.

📌 Steps:

• In the very beginning, select the rows that you wanted like the image below. For selecting the rows hold the CTRL key and click on the cells.
• Secondly, press the CTRL, SHIFT, and + (plus) keys together.

• Subsequently, the Insert dialog wizard pops out. Choose the Entire row and hit OK.

Finally, multiple blank rows are added before the selected rows, like in the image below.

Read More: How to Insert Multiple Blank Rows in Excel

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Conclusion

That’s all about today’s session. And these are some easy methods to insert a blank row after every nth row in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.

Related Articles

• Shortcuts to Insert New Row in Excel
• How to Insert Row Below in Excel
• How to Insert a Total Row in Excel
• Excel Formula to Insert Rows Between Data
• Cannot Insert Row in Excel
• Excel Fix: Insert Row Option Greyed Out in Excel
• VBA to Insert Row in Excel
• Excel VBA: Insert Row with Values
• Insert Rows in Excel Based on Cell Value with VBA
• VBA Macro to Insert Row in Excel Based on Criteria
• Excel Macro to Add Row to Bottom of a Table
• Macro to Insert Multiple Rows in Excel
• Macro to Insert Row and Copy Formula in Excel