How to Insert Rows Automatically in Excel (3 Ways)

While working in Excel, we may need to insert rows automatically in our dataset. Excel provides some features to do that job. Today, in this article, we will learn some methods to insert rows automatically in Excel


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


3 Useful Methods to Insert Rows Automatically in Excel

Consider a situation where you are given the Name, Region, and Rank of some sales representatives. We need to add some extra data to the dataset. So we need to insert a row into the dataset automatically. In this article, we will discuss three different methods to insert rows automatically.

Apply the Manual Method to Insert Rows Automatically


1. Apply the Manual Method to Insert Rows Automatically

One of the easiest ways to insert rows is to use the mouse. Using the mouse, we can insert rows variously. Let’s learn those ways!

Step 1:

  • Select a row where you want to insert a new row. Right-click on the mouse to open options. From the available options, click on Insert.

Apply the Manual Method to Insert Rows Automatically

  • A dialog box will open up. Select the Entire Row radio button because we want to insert an entire row. Click OK to continue.

Apply the Manual Method to Insert Rows Automatically

  • A new row is inserted automatically!

Apply the Manual Method to Insert Rows Automatically

Step 2:

  • Using a mouse you can insert rows in another way.
  • Click on the row serial numbers. Right-click on the mouse to open options.
  • From the options, select

Apply the Manual Method to Insert Rows Automatically

  • Excel will create a new row instantly!

Apply the Manual Method to Insert Rows Automatically

Step 3:

  • The third way to create rows using a mouse is also very easy. Select a row where you want to insert a new blank row.
  • In the Home Tab, go to Cells ribbon and click on Insert.
  • From the options, select Insert Sheet Rows

Apply the Manual Method to Insert Rows Automatically

  • And a new row is created automatically

Apply the Manual Method to Insert Rows Automatically


2. Use Keyboard Shortcuts to Insert Rows Automatically in Excel

Step 1:

  • Keyboard shortcuts are a great time saver when you want to insert rows automatically.
  • Click on a row above where you want a new row.
  • Press “Ctrl+Shift+ +” to insert a new row.

Use Keyboard Shortcuts to Insert Rows Automatically in Excel

  • Look at the dataset. We have got our new row.

Use Keyboard Shortcuts to Insert Rows Automatically in Excel

Step 2:

  • There is another shortcut to insert rows automatically. Click on the row in the dataset
  • Press “Alt+I+R

Use Keyboard Shortcuts to Insert Rows Automatically in Excel

  • We have a new row in our dataset.

Use Keyboard Shortcuts to Insert Rows Automatically in Excel


3. Insert VBA Codes to Insert Rows Automatically in Excel

We can use a simple VBA macro to insert rows automatically. Let’s learn this method by following these steps.

Step 1:

  • Press Alt+ F11 to open the VBA
  • In the VBA window, click on Insert.
  • Select Module from the option.

Insert VBA Codes to Insert Rows Automatically in Excel

Step 2:

  • A new module window appears. Write the code here. You can just copy-paste the code from this article. The code is,
Public Sub InsertRow()
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown
End Sub
  • Close the module window.

Insert VBA Codes to Insert Rows Automatically in Excel

Step 3:

  • Now from the Developer tab, click on Macros.

Insert VBA Codes to Insert Rows Automatically in Excel

  • Another window appears. Click on the code and then click on Options.

Insert VBA Codes to Insert Rows Automatically in Excel

  • Write the shortcut key for inserting new rows automatically. We have chosen “Ctrl+ Shift + R
  • Continue by clicking OK

Insert VBA Codes to Insert Rows Automatically in Excel

  • Click on Run to run the code.

Insert VBA Codes to Insert Rows Automatically in Excel

  • While the code is now active, Press Ctrl+ Shift + R to create new rows.

Insert VBA Codes to Insert Rows Automatically in Excel


Things to Remember

⏩  You have to select the cell above where you want to insert a row automatically.


Conclusion

Inserting rows automatically in excel is one of the easiest tasks to do if done correctly. We hope this article proves useful to you. Do comment if you have any questions or queries.


Similar Articles to Explore

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo