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.
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.
- A dialog box will open up. Select the Entire Row radio button because we want to insert an entire row. Click OK to continue.
- A new row is inserted 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
- Excel will create a new row instantly!
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
- And a new row is created 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.
- Look at the dataset. We have got our new row.
Step 2:
- There is another shortcut to insert rows automatically. Click on the row in the dataset
- Press “Alt+I+R”
- We have a new row in our dataset.
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.
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.
Step 3:
- Now from the Developer tab, click on Macros.
- Another window appears. Click on the code and then click on Options.
- Write the shortcut key for inserting new rows automatically. We have chosen “Ctrl+ Shift + R”
- Continue by clicking OK
- Click on Run to run the code.
- While the code is now active, Press Ctrl+ Shift + R to create new rows.
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.