Create or delete an Excel table (previously a list) in a worksheet

When you create a table (previously known as list) in a Microsoft Excel worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.), you can manage and analyze the data in that table independently of data outside the table. For example, you can filter table columns, add a row for totals, apply table formatting, and publish a table to a server that is running Windows SharePoint Services 3.0 or SharePoint Foundation 2010.

Data in an Excel table

If you no longer want to work with your data in a table, you can convert the table to a regular range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of data while keeping any table style formatting that you applied. When you no longer need a table and the data that it contains, you can delete it.

Note    Excel tables should not be confused with the data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) that are part of a suite of what-if analysis commands.

What do you want to do?


Create a table

You can use one of two ways to create a table. You can either insert a table in the default table style or you can format your data as a table in a style that you choose.

Insert a table using the default table style

  1. On a worksheet, select the range of cells that you want to include in the table. The cells can be empty or can contain data.
  2. On the Insert tab, in the Tables group, click Table.

Tables group on the Insert tab

Keyboard shortcut  You can also press CTRL+L or CTRL+T.

  1. If the selected range contains data that you want to display as table headers, select the My table has headers check box.

Table headers display default names if you do not select the My table has headers check box. You can change the default names by selecting the default header that you want to replace, and then typing the text that you want.

Tips

  • After you create a table, the Table Tools become available, and a Design tab is displayed. You can use the tools on the Design tab to customize or edit the table. Note that the Design tab is only visible when a cell in the table is selected.
  • Unlike lists in Office Excel 2003, a table does not have a special row (marked with *) for quickly adding new rows.

Insert a table using a style of your choice

  1. On the worksheet, select a range of empty cells or cells that contain the data that you want to quickly format as a table.
  2. On the Home tab, in the Styles group, click Format as Table.

Styles group on the Home tab

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

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

Note    Custom table styles are available under Custom after you create one or more of them.

Tips

  • After you create a table, the Table Tools become available when a cell is selected in the table, and a Design tab is displayed. You can use the tools on the Design tab to customize or edit the table.
  • Unlike lists in Excel 2003, a table does not have a special row (marked with *) for quickly adding new rows.

Top of Page Top of Page

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.

Tip    This displays the Table Tools, adding the Design tab. A cell in the table must be selected for the Design tab to be visible.

  1. On the Design tab, in the Tools group, click Convert to Range, and then click Yes.

Tools group on the Design tab

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

Tips

  • You can also right-click the table, point to Table, and then click Convert to Range.
  • Immediately after you create a table, you can also click Undo Button Image on the Quick Access Toolbar to convert that table back to a range.

Top of Page Top of Page

Delete a table and its data

If you no longer want to work with a table or the data that it contains, you can delete it.

  1. On a worksheet, select a table.

Tip    Press CTRL+A twice to select the entire table, including the table headers.

  1. Press DELETE.

Tip    You can also click Undo Button Image on the Quick Access Toolbar to delete a table that you just created.

Top of Page Top of Page

 
 
Applies to:
Excel 2010