How to Insert or Delete Rows and Columns from Excel Table

In this post, we shall learn how you can add new rows or columns to a table and how you can delete existing rows or columns from a table.

You can add a new row or new column in a table in two ways: add a new row/column at the end of the table or add a new row/column between existing rows/columns.

To add a new column at the end of the table, just select a cell in the column to the right of the table and enter some data in the cell. I enter 50010, press Enter.

Inserting new columns in Excel table

You see, a new column has been added automatically. The newly added column has a generic column heading: it is Column1. If you add another new column in this way: that will be Column2 and so on. You can just change this generic name to give it a meaningful column heading.

To add a new row in the table, select a cell in the row just below the table, and Enter some data in the cell

Remove column from Excel table

You see a new row has been added to the table.

If your table has the Total Row at the end of the table, you cannot add a new row to the table in this way. Select a cell in the row just below the Total Row of the table, enter some data and click OK. You see no new row is created.

Excel table, value outside of table

There is another way you can add new rows or columns to a table.

Observe the last cell of the table.

Expanding Excel table

You will find that there is a resize handle at the bottom right corner of the cell.

Move your mouse over that handle, your mouse pointer will turn into a diagonal line with two arrow heads. Click on the handle and drag it down to add new rows and columns.

Expanding Excel Table on the right

There is another nifty little way you can add new rows in a table. We have discussed it earlier. 10 points if you can guess it without reading further.

Read More: How to Insert, Edit & Delete Calculated Fields From Excel Pivot Table!

No peeking !!

Yes, it’s the “Tab” key on your keyboard. If you keep pressing it on the last row it will automatically add a new row after that. Now let’s see how to add rows or columns within the table. For example, I want to add a new column between Column B and Column C. Right-click on any cell in column C and take your mouse pointer over Insert, a submenu appears, and select “Table Columns to the Left”.

Adding columns to Excel table using shortcut menu

A new column has been created with a generic column heading. Using the same menu you will be able to add a whole bunch of rows in between too.

ways of adding or removing rows or columns from Excel table - summary

Deleting rows and columns is easy. On the right click use the same submenu, you can use the delete the selected row/column or rows/columns.

You can’t delete non-contiguous columns in this way. But you can delete non-contiguous rows from a table. Just select the cells from the non-contiguous rows that you want to delete, right-click on any selected cell, Delete and click on Table Rows.

Deleting a column or row from an Excel table

When you create a table, the Total Row is not activated. To display the Total Row, click on any cell within the table, click on the Design contextual tab, and put a checkmark next to the “Total Row” control. By default “Total Row” displays the Sum of the numbers of the first Numeric column. This table has two numeric columns, but Total Row by default is showing the Sum of the first numeric column. Select a cell in the Total row, a drop-down arrow appears in the cell.

Adding total row in Excel table

You can select a function to apply in that column.

The first option is None, if you choose None, no formula will be applied. The average function displays the average of the numbers in the column.

Count function displays the number of entries in the column. Blank cells are not counted. Count Numbers displays the number of numeric values in the column. Blank cells, Text cells, and error cells are not counted.

Then the Max function displays the maximum value in the column. Min displays the minimum value in the column.

You know about Sum, it displays the Sum of the values in the column.

StdDev displays the standard deviation of the values in the column. Standard deviation is a statistical term.

Var displays the variance of the values in the column. Variance is also a statistical term.

Click on More Functions option. Insert Function dialog box appears. We shall learn more about the Insert Function dialog box later.

So this summarizes our deal about rows and columns and manipulating them. Stayed tuned cause next we will be discussing some more awesome features of Excel Tables!

Download working file


Further Readings




Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

1 Comment
  1. I’m so thankful. I couldn’t figure out why my table wasn’t expanding when I hit enter. I didn’t realize I had to click and write something in the cell just below the table so that it expands after. I thought that if I wrote something in the last cell row and hit enter it would expand and it’s actually the other way around. I hope I got that correct. If a table needs to be expanded by a row, I must click the row outside the table and then hit enter after typing my data? If that’s not correct then I’m seriously screwed

Leave a reply