Excel provides some ways to insert rows, both manually right-clicking and automatically. For a few of those manually inserting a row is quite simple. But repeating the same stuff again and again, especially for a long table can be daunting. In this article, I will show you how to add a new row in the Excel table automatically.
Watch Video – Add New Row Automatically in a Table
Add New Row to Excel Table Automatically Using Excel Options
First, let’s take a sample dataset formatted as a table. For demonstration, I have selected the following dataset.
I have calculated the BMI of each person in column E using weight/(height)2 taken respectively from columns D and C. We now just have to follow this procedure so that Excel adds new rows where we need them.
Steps:
- Go to the File tab, then select Options to open up the Excel Options.
- Under the Proofing tab, select AutoCorrect Options.
An AutoCorrect window will pop up.
- In the AutoCorrect window, select AutoFormat As You Type.
- Then, check the Include new rows and columns in table and Fill formulas in tables to create calculated columns.
- Now, select OK and close the Excel Options.
- Go back to the table and under it just start typing a new row.
Upon completing you will see the new row is automatically added at the end of the table including the formula columns filled.
Add New Row to Excel Table Manually
The above-mentioned method only works in a scenario where you have to keep adding new rows at the end of a table. Now if you have to add a row between already existing rows, the methods shown below can help you.
For this, let’s take the same sample dataset as a table, where column E has been calculated using the BMI formula from the values of columns C and D.
Here, we are going to add new rows manually (but efficiently).
1. Add New Row by Using Keyboard Shortcuts in Excel
When it comes to shortcuts, there are two available to add a new row in a table. For this example, let’s say we want one between rows 9 and 10.
1.1 First Shortcut
Steps:
- Select a cell above which you want to insert the new row.
- Press Ctrl + Shift + =. It will insert a new row above it.
As you can see, a new row has been added with the formulas replicated.
1.2 Second Shortcut
There is another shortcut you can use instead of the above one. Simply follow these steps.
Steps:
- Select a cell above which you want to insert a new row.
- Press Alt+I on your keyboard.
- Then press R. It will insert a new row, like above.
Read More:Â Create Table in Excel Using Shortcut
2. Add New Row by Quick Access Toolbar
Using the Quick Access Toolbar, you can also achieve the same result. To do this:
- Select the row or a cell in a row before which you want to insert a row in the table. I have selected cell B10.
- Press and release the Alt Thus you will access the quick access toolbar.
- Press H (to access the Home tab) and then I (to go to Insert).
- Then press A to insert a table row above.
All of these methods are just different pathways to achieve the same output.
3. Add New Row Using VBA in an Excel Table
Aside from all of the methods mentioned above, you can easily add new rows by using VBA (Microsoft Visual Basic for Applications). Keep in mind that you need the Developer tab enabled on your ribbon. If you have one, just use the code and follow these steps.
Steps:
- Under the Developer tab, select Visual Basic (or press Alt+F11 for a shortcut).
- In the Visual Basic window, select Insert and then select Module.
- Inside the module, just write the code below.
Public Sub AddNewRow()
'Create rows below
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown
End Sub
- Close the VBA window and in the Developer tab, select Macros.
- In the Macros window, select Options (you can also run the macro through here, but for reusability, keep following the procedure and assign a shortcut).
- A Macro Options window will pop up. You can select a shortcut key here for feasibility while reusing the code. I am using Ctrl + Shift + N, You can replace Shift + N with a shortcut you prefer. Then select OK.
- After that, select a cell before the row where you want to insert one.
- Press Ctrl + Shift + N (or the key you have selected for the shortcut).
A new row will be added below the row. Now you can press the shortcut as many times as you want and as many places as you want. It will also replicate the formulas included in the columns.
Read More:Â How to Create a Table with Subcategories in Excel
💬 Things to Remember
- In the newly added rows, the column containing the formula shows zero division error, because of the lack of data in the rest of the cells. Once you input a value for all the cells the formula cell will display a value.
- In the first method, you can just keep filling up the rows and it will be added as a table row automatically.
- In the manual methods, rows will be inserted before the row you have selected (or the row your selected cell belongs to).
- If you use the VBA code, it creates a row after the cell or the row you have selected.
- In the macros window, you can skip the key assignment and just run the code from there. But for reusability, assign a shortcut.
Download the notebook with the template and VBA included below and try it for yourself.
Conclusion
These were the methods to add a new row in an Excel table automatically. Hope you have a good read and this guide has helped you.
Related Articles
- Create a Table in Excel Based on Cell ValueÂ
- How to Create a Table with Existing Data in Excel
- How to Create a Table Without Data in Excel
- How to Create a Table with Merged Cells in ExcelÂ
- How to Create a Table in Excel with Multiple Columns
- How to Make a Table in Excel with Lines
<< Go Back to Make a Table | Excel Table | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
