Resize a table by adding or removing rows and columns

After you create an Excel table in your worksheet, you can easily add or remove table rows and columns.

Note: The images in this article were created in Excel 2016. If you're using an earlier version, your display might be slightly different.

You can use the Resize command in Excel to add rows and columns to a table:

  1. Click anywhere in the table, and the Table Tools option appears.

  2. Click Design > Resize Table.

    using the resize table option from the Table Tools

  3. Select the entire range of cells you want your table to include, starting with the upper-leftmost cell.

    In the example shown below, the original table covers the range A1:C5. After resizing to add two columns and three rows, the table will cover the range A1:E8.

    Resize Table

    Tip: You can also click Collapse Dialog Button image to temporarily hide the Resize Table dialog box, select the range on the worksheet, and then click Expand dialog Button image .

  4. When you've selected the range you want for your table, press OK.

Other ways to add rows and columns

Add a row or column to a table by typing in a cell just below the last row or to the right of the last column, by pasting data into a cell, or by inserting rows or columns between existing rows or columns.

Start typing   

  • To add a row at the bottom of the table, start typing in a cell below the last table row. The table expands to include the new row. To add a column to the right of the table, start typing in a cell next to the last table column.

In the example shown below for a row, typing a value in cell A4 expands the table to include that cell in the table along with the adjacent cell in column B.

Adding a row by typing in the first row below a table

In the example shown below for a column, typing a value in cell C2 expands the table to include column C, naming the table column Qtr 3 because Excel sensed a naming pattern from Qtr 1 and Qtr 2.

Typing a value in a cell to the right of the table adds a column

Paste data   

  • To add a row by pasting, paste your data in the leftmost cell below the last table row. To add a column by pasting, paste your data to the right of the table's rightmost column.

If the data you paste in a new row has as many or fewer columns than the table, the table expands to include all the cells in the range you pasted. If the data you paste has more columns than the table, the extra columns don't become part of the table—you need to use the Resize command to expand the table to include them.

In the example shown below for rows, pasting the values from A10:B12 in the first row below the table (row 5) expands the table to include the pasted data.

Pasting data below the table expands the table to include it

In the example shown below for columns, pasting the values from C7:C9 in the first column to right of the table (column C) expands the table to include the pasted data, adding a heading, Qtr 3.

Pasting column data expands the table and adds a heading

Use Insert to add a row   

  1. To insert a row, pick a cell or row that's not the header row, and right-click. To insert a column, pick any cell in the table and right-click.

  2. Point to Insert, and pick Table Rows Above to insert a new row, or Table Columns to the Left to insert a new column.

If you're in the last row, you can pick Table Rows Above or Table Rows Below.

In the example shown below for rows, a row will be inserted above row 3.

Insert Table Rows Above

For columns, if you have a cell selected in the table's rightmost column, you can choose between inserting Table Columns to the Left or Table Columns to the Right.

In the example shown below for columns, a column will be inserted to the left of column 1.

Inserting a table column

Delete rows or columns in a table

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

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

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

    The Cells group on the Home tab

    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.

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.

    This displays the Table Tools, adding the Design tab.

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

    Excel Ribbon Image

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

    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 use Ctrl+Z or 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.

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.

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

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

Did you know?

If you don't have an Office 365 subscription or the latest Office version, you can try it now:

Try Office 365 or the latest version of Excel

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

See Also

Resize a table in Excel Online

How can I merge two or more tables?

Create an Excel table in a worksheet

Use structured references in Excel table formulas

Format an Excel table

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×