How to Insert or Delete Rows and Columns from Excel Table

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

📌 Steps:

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

Create an Excel Table

  • Finally, press the OK button and look at your Excel data.

We can see a down arrow added in the header of each column. This indicates that the table has been formed.


Insert Rows and Columns into Excel Table: 3 Ways

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 into an adjacent cell of the last row or column of your table, you will get a new row or column automatically.

📌 Steps:

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

Add new row in Excel table by entering data

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

Insert new column in Excel table by entering new data

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

Read More: How to Make an Excel Table Expand Automatically


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.

📌 Steps:

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

Insert rows in Table using Resize Handle tool

  • Release the mouse button.

We see two new rows added.

  • Similarly, drag the resize handle in the right direction to add a new column.

Insert columns in Excel Table using Resize Handle tool

  • Look at the dataset again.

Two new columns are inserted into the table successfully.

Read More: How to Extend Table in Excel


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 be applied 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.

📌 Steps:

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

Insert rows and columns from the Context Menu

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 be added 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.

Insert multiple rows from the context menu

  • Look at the dataset.

Two new rows are added in the same way.

Read More: How to Mirror Table in Excel


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.

📌 Steps:

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

Insert options of rows and columns in Excel table from the ribbon commands

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 the range C5:D5.
  • Choose Insert Table Columns to the Left option from Insert.

Insert multiple columns in Excel table from the ribbon commands

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

📌 Steps:

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

Use Tab button to insert new rows

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

Read More: How to Use Sort and Filter with Excel Table 


Delete Rows and Columns from Excel Table: 3 Methods

In this section, we will show how to delete existing rows and columns from an Excel table.

1. Delete Any Rows and Columns of Excel Table from Context menu

In this method, we will delete rows and columns from the Excel table using the Context Menu.

📌 Steps:

  • First of all, move the cursor to any cells of the table.
  • Next, press the right button of the mouse.
  • Then, the context menu appears.
  • Now find out the Delete option.

Delete options of rows and columns from the Context menu

We get two options under Delete.

One is for columns and the other for rows.

  • To delete rows, we choose Table Rows option.

Delete rows of Excel table

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.

Delete columns of Excel table

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.

Delete multiple columns of Excel table from Context Menu

Read More: How to Remove Format As Table in Excel


2. Use Delete Command from Home Ribbon

In this method, we will delete rows and columns from the options of Ribbon commands.

📌 Steps:

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

Delete options from the ribbon commands

Here, we will get two options for deleting the table rows and columns.

  • Choose the Delete Table Rows option.

Delete rows of Excel table from Ribbon commands

We can see that row 9 has been removed.

  • Now, we will choose the Delete Table Columns options for deleting columns.

Delete columns of table from Ribbon commands

column D has been removed.

We can also remove multiple rows and columns. Just select multiple cells and follow the above process.

Read More: How to Remove Table in Excel


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.

📌 Steps:

  • First, we want to remove the row from the table. Move the Resize Handle upwards.

Delete rows of table by Resize handle tool

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.

Delete columns of Excel table by Resize handle tool

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.

📌 Steps:

  • 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.
  • Press the Enter button.

  • We can see a new row is added to the table.

Expand formula with the insertion of new rows in Excel table

We can see the corresponding cell of that row of the savings column containing the formula.


Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.


Conclusion

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 give your suggestions in the comment box below.


Further Readings


<< Go Back to Edit Table | Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

3 Comments
  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

    • 1

      2
      Hello Bombshellshock,
      You are absolutely correct. To expand the table by a row, click the row just below the table and enter your desired data. After inserting data press Enter and your job will be done.
      If this reply doesn’t give a proper answer to your problem, then send the Excel file to [email protected]. Moreover, you can find the Excel file here.

  2. Hello BOMBSHELLSHOCK, first, you need to convert your dataset into Excel table format. To do that, you have to select your whole dataset and go to the Insert tab on the ribbon. After that, select Table from the Tables group. As a result, it will convert your dataset into a table. Then, you can write something in the last row and column and press Enter. As a result, Excel will insert new rows and columns into your Excel table. So, to perform the whole process, you must convert your dataset into a table. Mind it.
    If you face any more problems, you can share them with us. We are happy to deal with it and provide you with a proper solution.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo