How to Insert Row Below in Excel (5 Methods)

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.

How to Insert Row Below in Excel


Download Practice Workbook

Download the following workbook to practice by yourself.


5 Effective Methods in Excel to Insert Row Below

1. Excel VBA Method to Insert a Row Below

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.

Excel VBA Method to Insert a Row Below

  • Next, select Module under the Insert tab.

Excel VBA Method to Insert a Row Below

  • 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
Excel VBA Method to Insert a Row Below
  • After that, close the Visual Basic window.
  • Now, select cell D5.

Excel VBA Method to Insert a Row Below

  • 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.

Read More: How to Insert Row in Excel ( 5 Methods)


2. Excel Insert a Row After Every Other Row

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 Blank Column and Sort feature to Insert a row after every other row.

Excel Insert a Row After Every Other Row

STEPS:

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

Excel Insert a Row After Every Other Row

  • It’ll simply add a column on the left.

Excel Insert a Row After Every Other Row

  • Select cell A4.
  • There, type Blank Column.

Excel Insert a Row After Every Other Row

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

Excel Insert a Row After Every Other Row

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

Excel Insert a Row After Every Other Row

  • Now, select the range of cells except the header.

Excel Insert a Row After Every Other Row

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

Excel Insert a Row After Every Other Row

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

Excel Insert a Row After Every Other Row

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

Excel Insert a Row After Every Other Row


2.2 Insert 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 (11 Methods)


3. Enter Row Below 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.

Enter Row Below Blank Cell in Excel

STEPS:

  • Firstly, select cell F5 and type the formula:
=IF(B4<>"","",1)

Enter Row Below Blank Cell in Excel

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

Enter Row Below Blank Cell in Excel

  • Now, select the entire Column F.

Enter Row Below Blank Cell in Excel

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

Enter Row Below Blank Cell in Excel

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

Enter Row Below Blank Cell in Excel

  • 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.

Read More: How to Insert a Row within a Cell in Excel (3 Simple Ways)


Similar Readings


4. Excel Insert Row Using Subtotal Feature

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.

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

  • 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.

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

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

  • And then, a blank row will get inserted after every Salesman name.

  • After that, select the range of cells.

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

Excel Insert Row Using Subtotal Feature

  • 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 (4 Easy Methods)


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

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

Excel VBA to Place a Row at the Bottom of Table

STEPS:

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

Excel VBA to Place a Row at the Bottom of Table

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

Excel VBA to Place a Row at the Bottom of Table

  • 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

Excel VBA to Place a Row at the Bottom of Table

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

Excel VBA to Place a Row at the Bottom of Table

  • 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.

Read More: Excel Macro to Add Row to the Bottom of a Table


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.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo