How to Insert Row Below in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to place a blank row in our Excel worksheet to input missed data. In this article, you’ll get to know the methods in Excel to Insert Row Below.

To help you understand better, I’m going to use a sample dataset as an example. The following dataset represents Salesman, Product, and Net Sales.

Table of Contents hide

1. Inserting a Row Below Using Excel VBA

2. Inserting a Row After Every Other Row in Excel

2.1. Excel Blank Column and Sort Feature to Insert Row

2.2. Inserting Row with Excel VBA Code

3. Entering a Row Below the Blank Cell in Excel

4. Using Subtotal Feature to Insert Row

5. Applying Excel VBA to Place a Row at the Bottom of the Table

Conclusion

1. Inserting a Row Below Using Excel VBA

We can easily add a row under the selected cell in Excel using VBA code. In this method, we’ll use VBA to Insert a Row Below.

STEPS:

First, select the Visual Basic feature under Developer the tab.

Next, select Module under the Insert tab.

A window will pop out.
There, copy the Code given below and paste it into the Module window.

Sub PlaceRowBelow()
Dim rnge As Range
Application.ScreenUpdating = False
Set rnge = ActiveCell.Offset(1, 0)
rnge.EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.ClearFormats
Application.ScreenUpdating = True
End Sub

After that, close the Visual Basic window.
Now, select cell D5.

Then, select Macros under the Developer tab.

There, select the Macro name ‘PlaceRowBelow’.
And then, press Run.

Finally, it’ll add a row below the selected cell.

2. Inserting a Row After Every Other Row in Excel

This method will add a Row after Every Other Row in Excel.

2.1. Excel Blank Column and Sort Feature to Insert Row

Here, we’ll make use of the Blank Column and Sort feature to Insert a row after every other row.

STEPS:

First of all, select the leftmost column.
Next, right-click on the mouse and select the Insert option from the list.

It’ll simply add a column on the left.

Select cell A4.
There, type Blank Column.

Next, fill up the Column serially till the end of the data just like the following picture.

Again, fill up the column serially like it’s shown in the image below.

Now, select the range of cells except the header.

Then, right-click on the mouse.
There, select Sort Smallest to Largest from the Sort options.

After that, you’ll see that your dataset is being rearranged among itself.

Finally, just Delete the Blank Column and you’ll get your desired output.

2.2. Inserting Row with Excel VBA Code

Another process to add rows after every other row is with VBA code.

STEPS:

In the beginning, select the range of cells you want to work with.

Next, select the Visual Basic feature under the Developer tab.

Then, select Module under the Insert tab.

A window will pop out.
There, copy the Code given below and paste it into the Module window.

Sub PlaceRows()
Dim rnge As Range
Dim RowNumber As Integer
Dim x As Integer
Set rnge = Selection
RowNumber = rnge.EntireRow.Count
For x = 1 To RowNumber
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Next x
End Sub

After that, close the Visual Basic window and select Macros under the Developer tab.

There, select PlaceRows in the Macro name and press Run.

Eventually, you’ll get to see empty rows after every other row.

Read More: VBA to Insert Row in Excel

3. Entering a Row Below the Blank Cell in Excel

In this section, we’ll show how to use the IF function to Insert Rows after a Blank Cell in Excel.

STEPS:

Firstly, select cell F5 and type the formula:

=IF(B4<>"","",1)

Next, press Enter and drag it to the last row of your dataset.

Now, select the entire Column F.

Then, select Find from Find & Select options in the Editing group under the Home tab.

A dialogue box will pop out.
There, type 1 in Find what.
After that, press Find All.

The dialogue will expand like it’s shown in the image below.
There, select the rows with Value 1 and press Close.

And then, you’ll see that the cells with value 1 are being selected automatically.

Now, press the keys ‘Ctrl’ and ‘+’ together.
There, select the Entire row option from the pop-up dialogue box and press OK.

In the end, you’ll get to see your expected outcome just like the following image.

Similar Readings

4. Using Subtotal Feature to Insert Row

Here, we’ll show how to Insert a Row after every Salesman name in Excel.

STEPS:

Firstly, select the range of cells you want to work with.

Next, select the Subtotal feature from the Outline group under the Data tab.

A dialogue box will pop out.
There, select Salesman from ‘At each change in’ list, Count from ‘Use function’ list, check Net Sales in ‘Add subtotal to’ and keep the rest as it is.
Finally, press OK.

After pressing OK, you’ll get to see your dataset like the image below.

Now, select Go To Special from the Find & Select options in the Editing group under the Home tab.

A dialogue box will pop out.
There, check only the Numbers option in Formulas and press OK.

After pressing OK, you’ll see that all the count numbers are being selected.

Now, press down the ‘Ctrl’ and ‘+’ keys together.
There, select the Entire row in the pop-up dialogue box and press OK.

Then, a blank row will be inserted after every Salesman name.

After that, select the range of cells.

Now, select Subtotal from the Outline group under the Data tab.

Press Remove all in the pop-up dialogue box.

And finally, you’ll see the desired result.

Read More: How to Insert a Total Row in Excel

5. Applying Excel VBA to Place a Row at the Bottom of the Table

In this method, we’ll show how to add an empty Row at the Bottom of a Table in Excel.

STEPS:

First, select the Visual Basic feature under the Developer tab.

A window will pop out.
There, select Module under the Insert tab.

Another window will pop out.
There, copy the Code given below and paste it into the Module window.

Sub PlaceRowUnderTable()
Dim sheet As Worksheet
Set sheet = ActiveSheet
Table_Title = InputBox("Table Title: ")
Dim list As ListObject
Set list = sheet.ListObjects(Table_Title)
list.ListRows.Add
End Sub

After that, close the Visual Basic window.
Then, select Macros under the Developer tab.

There, select PlaceRowUnderTable in Macro name and press Run.

A dialogue box will pop out.
Type Table1 in Table Title and press OK.

Lastly, you’ll get to see an empty row under the Table.

Download Practice Workbook

Download the following workbook to practice by yourself.

Inserting Row Below.xlsm

Conclusion

Now, you will be able to Insert a Row Below in Excel using the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

How to Insert Row Below in Excel (5 Methods)

1. Inserting a Row Below Using Excel VBA

2. Inserting a Row After Every Other Row in Excel

2.1. Excel Blank Column and Sort Feature to Insert Row

2.2. Inserting Row with Excel VBA Code

3. Entering a Row Below the Blank Cell in Excel

4. Using Subtotal Feature to Insert Row

5. Applying Excel VBA to Place a Row at the Bottom of the Table

Conclusion

Related Articles

Leave a reply Cancel reply