How to Add a New Row in an Excel Table

Watch Video – Add a New Row Automatically in a Table

Below is the dataset, which calculates each person’s BMI in column E using weight/(height)² taken respectively from columns D and C.

Adding a New Row Automatically

Steps:

An AutoCorrect window will pop up.

In the AutoCorrect window, select AutoFormat As You Type.
Check Include new rows and columns in table and Fill formulas in tables to create calculated columns.

The new row is automatically added at the end of the table, including the formula columns filled.

Adding a New Row Manually

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.

Steps:

As you can see, a new row has been added with the formulas replicated.

Steps:

Steps:

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.

Steps:

Under the Developer tab, select Visual Basic (or press Alt+F11 for a shortcut).

Public Sub AddNewRow()
'Create rows below

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

End Sub

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.
Select OK.

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.