How to Insert Rows Automatically in Excel (4 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.


4 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 rows into the dataset automatically.

Dataset to Insert Rows in Excel Automatically

In this article, we will discuss 4 different methods to insert rows automatically.


1. Inserting Rows Manually

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!

Steps:

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

How to Insert Rows in Excel Automatically

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

How to Insert Rows in Excel Automatically

  • As a result, a new row will be inserted automatically!

Alternate 1:

Using a mouse you can insert rows in another way. For this:

  • Click on the row serial numbers.

  • Then right-click on the mouse to open options.
  • From the options, select Insert.

How to Insert Rows in Excel Automatically

  • Excel will create a new row instantly!

Alternate 2:

The third way to create rows using a mouse is also as simple as the previous ones.

  • Select a row where you want to insert a new blank row.
  • In the Home Tab, go to the Cells ribbon and click on Insert.
  • From the options, select Insert Sheet Rows.

How to Insert Rows in Excel Automatically

  • And a new row is created automatically.


2. Inserting Multiple Rows Automatically

If you want to insert multiple rows in your data table, it’s not a smart approach to insert them individually. Rather you can insert multiple rows at the same time.

Steps:

  • First, press the CTRL key, hold it and keep selecting the rows before which you want to insert rows.

How to Insert Rows in Excel Automatically

  • Then right-click on your mouse and a menu list will appear like the image below. From here, select Insert.

How to Insert Rows in Excel Automatically

  • Hence, multiple rows will be inserted automatically just to the upper position of the selected rows.


3. Use Keyboard Shortcut to Insert Rows Automatically

Keyboard shortcuts are a great time saver when you want to insert rows automatically. This procedure is much quicker than the previous ones.

Steps:

  • Click on a row above where you want a new row.
  • Press “CTRL, SHIFT, (+)” to insert a new row.

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

How to Insert Rows in Excel Automatically

Alternate:

There is another shortcut to insert rows automatically.

  • Click on the row in the dataset.
  • Press “ALT+I+R”.

How to Insert Rows in Excel Automatically

  • We have a new row in our dataset just up to the selected row.


4. Assign VBA Code 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.

Steps:

  • First of all, press ALT+ F11 to open the Visual Basic Editor window.
  • In the VBA window, click on Insert > and select Module from the option.

  • A new module window appears. Write the corresponding code here to insert rows. You can insert the following code.
Public Sub Inserting_Row()

ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert SHIFT:=xlDown

End Sub

VBA Code to Insert Rows in Excel Automatically

  • Next, press ALT+Q to close the module window.
  • After that, press ALT+F8.
  • Another pop-up named Macro will appear on the worksheet. Click click on Options on the pop-up.

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

How to Insert Rows in Excel Automatically

  • Click on Run to run the code.

  • While the code is now active, Press “CTRL + SHIFT + R” to create new rows.

  • Hence, you will see a new row has been inserted to your data table.


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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo