How to Add New Row Automatically in an Excel Table

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.

Dataset to Add New Row Automatically in an Excel Table

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.


  • Go to the File tab, then select Options to open up the Excel Options.
  • Under the Proofing tab, select AutoCorrect Options.

Autocorrect options to Add New Row Automatically in an Excel Table

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.

autocorrent box to Add New Row Automatically in an Excel Table

  • 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.

New Row Automatically Added in an Excel Table

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


  • Select a cell above which you want to insert the new row.

  • Press Ctrl + Shift + =. It will insert a new row above it.

new row added in between using keyboard shortcuts

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.


  • 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.

new row in between excel table

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.

new row added in excel table using quick access toolbar

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.


  • 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.

inserting a module in vba window

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

macro list in excel

  • 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.

setting macro shortcut keys

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

new row automatically added using vba in excel

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

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


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

<< Go Back to Make a Table | Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF