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.

How to Insert Row Below in Excel


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 the Developer 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: Shortcuts to Insert New Row in Excel


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.

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

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

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


2.2. Inserting Row with Excel VBA Code

Another process to add rows after every n-th 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: How to Insert Rows in Excel Automatically


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.

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: Excel Formula to Insert Rows Between Data


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.

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

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

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 Multiple Blank Rows 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.

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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