- Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.
In this post, you will learn how to resize a table by inserting or deleting rows and columns. This will be examined for both Excel for Windows and Excel for the Web.
Adding or deleting table rows and columns is a simple task once you have made an Excel table in your worksheet.
Using the Resize Command
The main way to insert rows and columns to a table is by using the Resize command:
- Select any point in the table then the Table Tools option displays.
- Press Design > Resize Table.
- Choose the whole array of cells you prefer your table to contain. Start with the upper-leftmost cell. In the illustrated example underneath, the first table occupies the range A1:C5. When it has been resized to insert three rows and two columns, the table will then span the range A1:E8.
Tip: You can even press Collapse Dialogue to momentarily cover the Resize Table dialogue box, pick the specific worksheet range, and then select Expand dialogue .
- Once you’ve chosen your desired range, click OK.
Alternative ways to add rows and columns to resize a table
Insert a row or column to a table by entering in a cell right beneath the the final row. Otherwise, it would be to the right of the last column. Another way is by pasting data directly into a cell. Alternatively, by adding rows or columns amongst current rows or columns.
To append a row at the base of the table, begin typing in a cell beneath the final table row. The table enlarges to contain the new row. To insert a column to the right of the table, begin writing in a cell beside the last table column.
With the example displayed below for a row, inputting a value in cell A4 grows the table to occupy that cell in the table together with the nearby cell in column B.
For the example indicated below for a column, entering a value in cell C2 extends the table to comprise column C. Effectively, this labels the table column Qtr 3 since Excel deduced a naming pattern from Qtr 1 and Qtr 2.
- To insert a row by pasting, paste your data in the leftmost cell. This will reside beneath the final table row. To create a column by pasting, paste your data immediately to the right of the table’s rightmost column.
If your pasted data in a new row has enough or reduced columns than the table, the table extends to take account every cell in the range you pasted. However, if your pasted data includes excessive columns than the table, the surplus columns are omitted from the table. You must use the Resize command to enlarge the table to contain them.
The example presented below for rows depicts that, pasting the values from A10:B12 in the first row underneath the table (row 5) broadens the table to consider the pasted data.
In the example displayed below for columns, pasting the values from C7:C9 in the first column to right of the table (column C) widens the table to embody the pasted data. In effect, this creates a new heading, Qtr 3.
Use Insert to add a row
- To add a row, choose a cell or row other than the header row, and right-click. To insert a column, select any cell in the table and right-click.
- Aim at Insert, and click Table Rows Above to append a new row, or Table Columns to the Left to produce a new column.
If you’re in the final row, you can choose Table Rows Above or Table Rows Below.
With the example illustrated below for rows, a row will be added above row 3.
For columns, if you have a cell picked in the table’s rightmost column, you can select between adding Table Columns to the Left or Table Columns to the Right.
In the example displayed below for columns, a column will be added to the left of column 1.
Delete table rows or columns to resize a table
- Choose one or further table rows or table columns that you prefer to erase. You can even simply pick one or more cells in the table rows or table columns that you seek to delete.
- On the Home tab, in the Cells group, press the arrow beside Delete. Then, select Delete Table Rows or Delete Table Columns. Additionally, you can right-click one or more rows or columns, move to Delete on the shortcut menu, and then press Table Columns or Table Rows. Alternatively, you can right-click one or more cells in a table row or table column, aim at Delete, and then select Table Rows or Table Columns.
Remove duplicates from a table to resize it
Like you can erase duplicates from any highlighted data in Excel, you can conveniently delete duplicates from a table.
- Select anywhere in the table. This presents the Table Tools, creating the Design tab.
- On the Design tab, in the Tools group, press Remove Duplicates.
- In the Remove Duplicates dialogue box, below Columns, choose the columns with your target duplicates to remove. You can even select Unselect All and then pick your desired columns or press Select All to choose every single column.
Note: Removed duplicates are invariably erased from the worksheet. If you unintentionally delete data that you wanted to retain, you can use Ctrl+Z or press Undo on the Quick Access Toolbar to restore the removed data. You might even want to deploy conditional formats to earmark duplicate values prior to you deleting them. For further information, see Add, change, or clear conditional formats.
Delete blank rows from a table to resize it
- First of all, check that the active cell lies within a table column.
- Select the arrow in the column header.
- To filter for blanks, in the AutoFilter menu at the top of the list of values, deselect (Select All), and then at the bottom of the list of values, click (Blanks). Note: The (Blanks) checkbox is available solely on the condition that the range of cells or table column includes a minimum of one blank cell.
- Pick the blank rows in the table, and then press CTRL+- (hyphen).
You can use a similar procedure for filtering and deleting blank worksheet rows. For additional details about how to filter for blank rows in a worksheet, see Filter data in a range or table.