Create or delete an Excel table in a worksheet

When you create a table in an Excel worksheet, it's not only easier for you to manage and analyze its data, but you also get built-in filtering, sorting, and row shading.

Note: Excel tables shouldn't be confused with the data tables that are part of a suite of What-If Analysis commands (Data Tools, on the Data tab). See Introduction to What-If Analysis for more information.

To create a table by using the default table style, do this:

Note: To create a table from data on another worksheet, see

1. Select the range of cells you want included in the table. The cells can be empty or can contain data.

2. Click Insert > Table.

   

   Tip: Keyboard shortcut : Press Ctrl+T or Ctrl+L.

3. If the top row of the selected range contains data you want to use as table headers, check the My table has headers box.

   

   If you don't check the My table has headers box, table headers with default names like Column1 and Column2 are added to your table above its data. You can modify default header names at any time. For more information, see Turn Excel headers on or off

Create a table in the style you want

1. Select the range of cells you want included in the table.

2. On the Home tab, click Styles > Format as Table.

   

   When you use Format as Table, Excel automatically inserts a table.

   1. Under Light, Medium, or Dark, click the table style you want to use. If the selected range contains data you want to use as table headers, click a table style that includes a header row.

   You can create your own table style to use in the current workbook. After you create a custom table style, it's available for the current workbook when you use Format as Table, under Custom. For more information, see Format an Excel table.

More about tables

• After you create a table, the Table Tools become available, and you'll see a Design tab. You can use the tools on the Design tab to customize or edit the table. Note that the Design tab is visible only when at least one or more cells in the table are selected.

• After you create a table, you'll see the Quick Analysis button next to it. Click this button to see tools that can help you analyze your table data, such as conditional formatting, sparklines, charts, or formulas.

• To add a row, select the last cell in the last row of the table, and press Tab.

  In the example shown here, pressing Tab with cell C4 selected expands the table to a fifth row, and moves the selection to the first column in the new row, cell A5.

  

• For more information on working with tables, see the links in the See Also section.

Delete a table

Delete a table without losing the data or table formatting

Delete the entire table

Clear the data but keep the table

Keep the data but remove the table

Delete a table without losing the data or table formatting

After you create a table, you might not want to keep working with the table functionality that it includes. Or you might want a table style without the table functionality. To stop working with your data in a table without losing any table style formatting that you applied, you can convert the table to a regular range of data on the worksheet.

1. Click anywhere in the table.

   This displays the Table Tools tab on the ribbon.

2. On the Design tab, in the Tools group, click Convert to Range.

   

   --OR--

   Right-click on the table and click Convert to Range.

   Table features are no longer available after you convert the table back to a range. For example, the row headers no longer include the sort and filter arrows, and structured references (references that use table names) that were used in formulas turn into regular cell references. For more information, see Using structured references in Excel tables.

Delete the entire table

1. Select the entire table by picking any of its header cells, and pressing Ctrl+A to select all its cells.

2. Press Delete.

Clear the data but keep the table

To keep the structure and formatting of the table, but delete the data, do this:

1. Select all the rows of the table except the headers.

2. Pick Clear > Clear Contents.

3. To clear the headers, select all the cells in its row and pick Clear > Clear Contents.

Keep the data but remove the table

To delete the table but keep your data, you can remove the table without losing the data or table format.

See Also

Overview of Excel tables

Total the data in an Excel table

Format an Excel table

Resize a table by adding or removing rows and columns

Excel table compatibility issues

Export an Excel table to SharePoint

Convert a table to a range

Filter data in an Excel table

Video: Create an Excel table