The Table is a wonderful feature of Excel. A table contains a group of data and we can use those data combinedly and perform any operation on the Table with a single click. Sometimes we need to insert or delete rows and columns from the Table. So, in this article, we are going to discuss how to insert new rows and columns or delete existing rows and columns from the Excel Table.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
How to Create an Excel Table?
The Excel Table is a range of data that are related and can operate any operation on that range in a single click. The Table must be a rectangular range.
In this section, we will show the procedure to create an Excel Table.
- First, select the desired range that we want to convert into a Table.
- Then, press Ctrl + T.
- The Create Table window appears after that.
- We can see our selected range in this window. If needed, we can change the range from here.
- We mark the My table has headers checkbox.
- Finally, press the OK button and look at the your Excel data.
We can see a down arrow added in the header of each column. This indicates that the table has been formed.
3 Ways to Insert Rows and Columns into Excel Table
Now, we will discuss inserting rows and columns in the below section.
1. Insert New Rows and Columns Adjacent to Existing Excel Table
In the first method, we will insert a new row or column next to the last row or column of the existing table.
1.1 Insert Data at a Cell Adjacent to Last Row or Last Column
If you enter new data to an adjacent cell of the last row or column of your table, you will get a new row or column automatically.
- Here is our table. We want to insert new rows next to Row 9 and columns next to Column D.
- Now, input any data at any cell of Row 10 that is adjacent to the existing table.
- Then, press the Enter button.
- Look at the dataset.
We can see a new row is added to the table.
- Now, go to Column E. Input data at Cell E5 adjacent to the table.
- Again, press the Enter button.
- We can see a new column is added to the table.
The header of the column is Column1 which is by default. Anyway, we can modify the header. In this way, we can insert more rows and columns according to our needs.
1.2 Use Resize Handle Feature
In this section, we will use the Resize Handle tool of Excel Table to insert new rows and columns.
- Look at the bottom-right corner of the Excel Table. A both-sided arrow is showing. This is the Resize Handle tool.
- Now, hover your mouse cursor on the resize handle and press the right or left key of the mouse. Then hold the key and drag it in the downward direction to add a new row.
- Release the mouse button.
We see two new rows added.
- Similarly, drag the resize handle in the right direction to add a new column.
- Look at the dataset again.
Two new columns are inserted into the table successfully.
2. Insert Rows and Columns Within Excel Table
In this method, we will show the insertion of new rows or columns at any location of the table. This method can also apply to insert new rows and columns next to the table.
2.1 Insert from Context Menu of Mouse Right-Click
Here, we will use the context menu of the mouse right-click to insert new rows and columns in the table.
- First, we move the cursor to Cell D9.
- Then, press the right button of the mouse. As a result, a Context Menu appears.
- We see the Insert option there.
- Now move the cursor on the Insert option and a drop-down comes.
We can see that there are four options in the drop-down. We need to choose the option based on which side we want to insert new rows and columns. Columns will be added on the left and right and rows will add in below or above the selected cell.
- Now, choose the Table Rows Above option.
Consequently, we can see a new row inserted above the selected cell.
- Again, we choose the Table Row Below option.
- Now, we will add the columns. For that, choose Table Columns to the Left option.
We can see a new column on the left side.
- Again, choose Table Column to the Right option.
In this case, a new column is inserted on the right side.
- We can also insert multiple rows or columns in this way at the same time.
- For that, choose Range D7:D8 and press the right button on the mouse.
- Now, choose the Table Rows Above option from the drop-down of the Insert option.
- Look at the dataset.
Two new rows are added in the same way.
2.2 Using Insert Command from Home Ribbon
Here, we will use the Ribbon commands to insert new rows and columns in the Excel table.
- First off, we click on the last cell of the table.
- From the Home tab, we will find the Cells group.
- We get the Insert option from the drop-down of the Cells group.
There are four options for Table rows and columns.
We will show two examples. One for rows and another one for columns.
- Choose the Insert Table Row Below option.
We can see a new row is added next to the table.
- Now, we will insert a column in the dataset. Choose Insert Table Columns to the Left option.
A new column is added. We can also insert multiple rows and columns at the same time in this method.
- Here, we choose Range C5:D5.
- Choose Insert Table Columns to the Left option from Insert.
- Look at the dataset.
Two new columns are inserted into the table. Similarly, we can insert multiple rows using the Row option of the Insert tab.
3. Insert New Row Before Last Row Using Tab Key from Keyboard
In this method, we will use the Tab key. The constraint of this method is- it will insert only a new row with no columns next to the table.
- Now, place the cursor at any cell of the table and press the Tab key again and again till we go to the last cell of the table. Or place the cursor on the last cell of the table, and then press the Tab button.
- Look at the dataset.
In this way, we can add only one single row. If we need to add more rows, we have to click the Tab key again and again.
3 Methods to Delete Rows and Columns from Excel Table
In this section, we will show how to delete existing rows and columns from an Excel table.
In this method, we will delete rows and columns from the Excel table using the Context Menu.
- First of all, move the cursor to any cells of the table.
- Then press the right button of the mouse.
- Then the Context Menu appears.
- Now find out the Delete option.
We get two options under Delete.
One is for columns and the other for rows.
- To delete rows, we choose Table Rows option.
Now, look at the dataset. Row 6 has been removed from the table and the next row comes at Row 6.
- Now, choose the Table Columns option.
We can see Column D is no more in the table.
- We can also delete multiple rows and columns in this way by choosing multiple cells first.
2. Use Delete Command from Home Ribbon
In this method, we will delete rows and columns from the options of Ribbon commands.
- Press any of the cells of the table.
- Choose the Cells group from the Home tab.
- Select the Delete option from the Cells drop-down.
Here, we will get two options for deleting the table rows and columns.
- Choose the Delete Table Rows option.
We can see that Row 9 has been removed.
- Now, we will choose the Delete Table Columns options for deleting columns.
Column D has been removed.
We can also remove multiple rows and columns. Just select multiple cells and follow the above process.
3. Delete Last Rows and Columns of Excel Table Using Resize Handle Feature
In this section, we will use the Resize Handle tool. This tool has special characteristics. It will delete rows and columns from the last cell, not from any location.
We already know about the Resize tool in the previous section. We also know how to use the Resize Handle tool.
- First, we want to remove the row from the table. Move the Resize Handle upwards.
We can see one row has been removed from the table.
- Similarly, if we move the Resize Handle tool to the leftwards, columns will be removed.
One column of the table has been removed as we go leftwards.
How to Insert Rows Together with Formula in an Excel Table
In this section, we will discuss a special feature of Excel Table. As we insert new rows in the table, the existing formula used in the table will expand with the rows.
- Here, we will use this sample table. We calculate the Savings by using a formula in the Savings column.
- Now, we insert new data next to the table to add a new row.
- And press the Enter button.
- We can see a new row is added to the table.
We can see the corresponding cell of that row of the Savings column containing the formula.
In this article, we discussed in detail how to insert or delete rows and columns from Excel Table. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.