Add or remove table rows and columns in an Excel table

After you create a Microsoft Office Excel table in your worksheet, you can easily add table rows and columns. You can quickly add a blank row at the end of the table, include adjacent worksheet rows or worksheet columns in the table, or insert table rows and table columns anywhere you want.

You can delete rows and columns as needed. You can also quickly remove rows that contain duplicate data from a table.

 Note   Adding and removing table rows and columns is different from adding and removing worksheet rows and columns. To add and remove worksheet rows and columns, see Insert or delete cells, rows, and columns.

What do you want to do?


Add a blank row at the end of the table

  • Press TAB in the last cell of the last row to add a blank row at the end of the table.

 Note   If a totals row is displayed in the table, pressing TAB in the last cell of the totals row does not add a new row.

Top of Page Top of Page

Include a worksheet row or worksheet column in a table

  • Do one of the following:
    • To include a worksheet row in the table, type a value or text in a cell that is directly below the table.
    • To include a worksheet column in the table, type a value or text in a cell that is directly adjacent to the right of the table.
    • To include worksheet rows or worksheet columns by using the mouse, drag the resize handle at the lower-right corner of the table down to select rows and to the right to select columns.

Table resizing handle

Top of Page Top of Page

Resize a table

  1. Click anywhere in the table.

 Tip   This displays the Table Tools, adding the Design tab.

  1. On the Design tab, in the Properties group, click Resize Table.

Excel Ribbon Image

  1. In the Select the new data range for your table box, type the range that you want to use for the table.

 Tip   You can also click the Collapse Dialog button Button image  at the right end of the Select the new data range for your table box and then select the range that you want to use for the table on the worksheet. When you finish, click the Collapse Dialog button again to display the entire dialog box.

 Tip   To resize a table by using the mouse, drag the triangular resize handle at the lower-right corner of the table up, down, to the left, or to the right to select the range that you want to use for the table.

Table resizing handle

Top of Page Top of Page

Insert a table row or column

  1. Do one of the following:
    • To insert one or more table rows, select one or more table rows above which you want to insert one or more blank table rows.

 Tip   If you select a cell or range in the last row, you can also insert a row above or below that row.

  • To insert one or more table columns, select one or more table columns to the left of which you want to insert one or more blank table columns.

 Tip   If you select a cell or range in the last column, you can also insert a column to the left or to the right of that column.

  1. On the Home tab, in the Cells group, click the arrow next to Insert.

Excel Ribbon Image

  1. Do one of the following:
    • To insert table rows, click Insert Table Rows Above.
    • To insert a table row below the last row, click Insert Table Row Below.
    • To insert table columns, click Insert Table Columns to the Left.
    • To insert a table column to the right of the last column, click Insert Table Column to the Right.

 Tip   You can also right-click one or more table rows or table columns, point to Insert on the shortcut menu, and then select what you want to do from the list of options. Or you can right-click one or more cells in a table row or table column, point to Insert, and then click Table Rows Above or Table Columns to the Left.

Top of Page Top of Page

Delete rows or columns in a table

  1. Select one or more table rows or table columns that you want to delete.

 Tip   You can also just select one or more cells in the table rows or table columns that you want to delete.

  1. On the Home tab, in the Cells group, click the arrow next to Delete, and then click Delete Table Rows or Delete Table Columns.

Excel Ribbon Image

 Tip   You can also right-click one or more rows or columns, point to Delete on the shortcut menu, and then click Table Columns or Table Rows. Or you can right-click one or more cells in a table row or table column, point to Delete, and then click Table Rows or Table Columns.

Top of Page Top of Page

Remove duplicate rows from a table

Just as you can remove duplicates from any selected data in Excel, you can easily remove duplicates from a table.

  1. Click anywhere in the table.

 Tip   This displays the Table Tools, adding the Design tab.

  1. On the Design tab, in the Tools group, click Remove Duplicates.

Excel Ribbon Image

  1. In the Remove Duplicates dialog box, under Columns, select the columns that contain duplicates that you want to remove.

 Tip   You can also click Unselect All and then select the columns that you want or click Select All to select all of the columns.

 Note   Duplicates that you remove are deleted from the worksheet. If you inadvertently delete data that you meant to keep, you can click Undo Button Image on the Quick Access Toolbar to restore the deleted data. You may also want to use conditional formats to highlight duplicate values before you remove them. For more information, see Add, change, or clear conditional formats.

Top of Page Top of Page

Remove blank rows from a table

  1. Make sure that the active cell is in a table column.
  2. Click the arrow Filter drop-down arrow in the column header.
  3. To filter for blanks, in the AutoFilter menu at the top of the list of values, clear (Select All), and then at the bottom of the list of values, select (Blanks).

 Note   The (Blanks) check box is available only if the range of cells or table column contains at least one blank cell.

  1. Select the blank rows in the table, and then press CTRL+- (hyphen).

 Tip   You can use a similar procedure for filtering and removing blank worksheet rows. For more information about how to filter for blank rows in a worksheet, see Filter data in a range or table.

Top of Page Top of Page

 
 
Applies to:
Excel 2007