How to Insert a Row Below in Excel (5 Methods)

Below is a dataset representing Salesman, Product, and Net Sales.

How to Insert Row Below in Excel


Method 1 – Inserting a Row Below Using Excel VBA

STEPS:

  • Select the Visual Basic feature under the Developer tab.

Excel VBA Method to Insert a Row Below

  • Select Module under the Insert tab.

Excel VBA Method to Insert a Row Below

  • A window will pop out.
  • Enter the following formula 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
  • Close the Visual Basic window.
  • Select cell D5.

Excel VBA Method to Insert a Row Below

  • Select Macros under the Developer tab.

  • Select the Macro name ‘PlaceRowBelow’.
  • Press Run.

  • A row will be added below the selected cell.

Read More: Shortcuts to Insert New Row in Excel


Method 2 – Inserting a Row After Every Other Row in Excel

2.1. Excel Blank Column and Sort Feature to Insert Row

STEPS:

  • Select the leftmost column.
  • Right-click on the mouse and select the Insert option.

Excel Insert a Row After Every Other Row

  • It’ll add a column on the left.

Excel Insert a Row After Every Other Row

  • Select cell A4.
  • Type Blank Column.

Excel Insert a Row After Every Other Row

  • Fill up the Column serially until the end of the data, as shown in the image below.

Excel Insert a Row After Every Other Row

  • Fill up the column serially, as shown in the image below.

Excel Insert a Row After Every Other Row

  • Select the range of cells except the header.

Excel Insert a Row After Every Other Row

  • Right-click on the mouse.
  • Select Sort Smallest to Largest from the Sort options.

Excel Insert a Row After Every Other Row

  • You’ll see that your dataset is rearranged.

Excel Insert a Row After Every Other Row

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

Excel Insert a Row After Every Other Row

Read More: Insert Multiple Rows After Every Other Row in Excel


2.2. Inserting a Row with Excel VBA Code

STEPS:

  • Select the range of cells you want to work with.

  • Select the Visual Basic feature under the Developer tab.

  • Select Module under the Insert tab.

  • A window will pop out.
  • Enter the following formula 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

  • Close the Visual Basic window and select Macros under the Developer tab.

  • Select PlaceRows in the Macro name and press Run.

  • You’ll see empty rows, one after every other row.

Read More: How to Insert Rows in Excel Automatically


Method 3 – Entering a Row Below the Blank Cell in Excel

STEPS:

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

Enter Row Below Blank Cell in Excel

  • Press Enter and drag it to the last row of your dataset.

Enter Row Below Blank Cell in Excel

  • Select the entire Column F.

Enter Row Below Blank Cell in Excel

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

Enter Row Below Blank Cell in Excel

  • A dialogue box will pop out.
  • Type 1 in Find what.
  • Press Find All.

Enter Row Below Blank Cell in Excel

  • The dialogue will expand, as shown in the image below.
  • Select the rows with Value 1 and press Close.

  • You’ll see that the cells with value 1 are being selected automatically.

  • Press the keys ‘Ctrl’ and ‘+’ together.
  • Select the Entire row option from the pop-up dialogue box and press OK.

  • You’ll get to see your expected outcome, as shown in the image below.

Read More: Excel Formula to Insert Rows Between Data


Method 4 – Using the Subtotal Feature to Insert a Row

STEPS:

  • Select the range of cells you want to work with.

Excel Insert Row Using Subtotal Feature

  • Select the Subtotal feature from the Outline group under the Data tab.

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

  • You’ll get to see your dataset, just like the image below.

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

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

Excel Insert Row Using Subtotal Feature

  • You’ll see that all the count numbers are being selected.

Excel Insert Row Using Subtotal Feature

  • Press the ‘Ctrl’ and ‘+’ keys together.
  • Select the Entire row in the pop-up dialogue box and press OK.

  • A blank row will be inserted after every Salesman’s name.

  • Select the range of cells.

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

  • You’ll see the desired result.

Read More: How to Insert Multiple Blank Rows in Excel


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

STEPS:

  • 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.
  • Select Module under the Insert tab.

Excel VBA to Place a Row at the Bottom of Table

  • Another window will pop out.
  • Enter the following formula 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

  • Close the Visual Basic window.
  • Select Macros under the Developer tab.

Excel VBA to Place a Row at the Bottom of Table

  • Select PlaceRowUnderTable in Macro name and press Run.

  • A dialogue box will pop out.
  • Type Table1 and press OK.

  • You’ll get to see an empty row under the Table.


Download the Practice Workbook

Download the following workbook to practice.


Related Articles


<< Go Back to Insert Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo