How to Add New Row Automatically in an Excel Table

Get FREE Advanced Excel Exercises with Solutions!

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

Table of Contents hide

Add New Row to Excel Table Automatically Using Excel Options

Add New Row to Excel Table Manually

1. Add New Row by Using Keyboard Shortcuts in Excel

1.1 First Shortcut

1.2 Second Shortcut

2. Add New Row by Quick Access Toolbar

3. Add New Row Using VBA in an Excel Table

💬 Things to Remember

Conclusion

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

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.

💬 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 Practice Workbook

Download the notebook with the template and VBA included below and try it for yourself.

Add New Row Automatically in a Table.xlsm

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.

For more friendly and useful guides, try exploring Exceldemy.

How to Add New Row Automatically in an Excel Table

Add New Row to Excel Table Automatically Using Excel Options

Add New Row to Excel Table Manually

1. Add New Row by Using Keyboard Shortcuts in Excel

1.1 First Shortcut

1.2 Second Shortcut

2. Add New Row by Quick Access Toolbar

3. Add New Row Using VBA in an Excel Table

💬 Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply