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 in 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 new row/column at the end of the table or add 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 in 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 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.

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 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 check mark 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 that 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 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

Adding_and_Deleting_Rows_or_Columns

Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.