Table Definition and Features in Excel

Use tables to group related information

Definition and Use of Tables in Excel
Definition and Use of Tables in Excel. © Ted French

A table in Excel is a series of rows and columns in a worksheet that contains related data. In versions before Excel 2007, a table of this type was referred to as a List.

More specifically, a table is a block of cells — rows and columns — containing related data that have been formatted as a table using Excel's Table option on the Insert tab of the ribbon.

Formatting a block of data as a table makes it easy to carry out a variety of tasks on the table data without affecting other data in the worksheet. These tasks include:

  • Sorting data
  • Filtering data
  • Quick calculations, in which you enter one formula that is automatically applied to every cell in a column
  • Adding column totals to the last row in the table
  • Formatting the table data as a distinctive block, thus setting it off from other data in the worksheet

Inserting a Table

Although it is possible to create an empty table, it is easier to enter the data first before formatting it as a table. When entering the data, don't leave blank rows, columns, or cells in the block of data that will form the table. To create a table:

  1. Click any single cell inside the block of data.
  2. Click on the Insert tab of the ribbon.
  3. Click on the Table icon located in the Tables group. Excel selects the entire block of contiguous data and opens the Create Table dialog box.
  4. If your data has a heading row, check the My table has headers option in the dialog box.
  5. Click OK to create the table.

Table Features

The most notable features that Excel adds to the block of data are:

  • Drop-down menus containing sort, filter, and search options for each column in the table
  • Formatting the block of data with alternate shaded rows
  • The sizing handles located in the corners of the table
  • The Quick Analysis icon (in Excel 2013 and newer), which appears in the bottom right corner of the active cell when two or more cells in the table are selected. It makes it easy to analyze the data using:
    • Charts
    • Pivot tables
    • Running totals
    • Conditional formatting

Managing Table Data: Sort and Filter Options  

The sort and filter drop-down menus added to the header row make it easy to sort tables:

  • In ascending order
  • In descending order
  • By font or cell background color
  • Using a custom sort order

The filter options in the menus allow you to:

  • Show only the data that meets the criteria specified
  • Filter by the font or cell background color
  • Search for specific records by matching individual fields of data.

Managing Table Data: Add and Remove Fields and Records  

The sizing handle makes it easy to add or remove entire rows (records) or columns (fields) of data from the table. To do so:

  1. Click and hold down the mouse pointer on the sizing handle.
  2. Drag the sizing handle up or down or to the left or right to resize the table.

Data that is removed from the table is not deleted from the worksheet, but it is no longer included in table operations such as sorting and filtering.

Managing Table Data: Calculated Columns  

A calculated column allows you to enter a single formula in one cell in a column and have that formula automatically applied to all cells in the column. If you don't want the calculation to include all cells, delete the formula from those cells. If you only want the formula in the initial cell, use the undo feature to remove it from all other cells.

Managing Table Data: Total Row  

The number of records in a table can be totaled by adding a Total row to the bottom of the table. The total row uses the SUBTOTAL function to count the number of records.

Also, other Excel calculations such as Sum, Average, Max, and Min can be added using a drop-down menu of options. These additional calculations also make use of the SUBTOTAL function.

 To add a Total row:

  1. Click anywhere in the table
  2. Click on the Design tab of the ribbon
  3. In the Table Style Options group, click on the Total Row check box to select it.

The total row appears as the last row in the table and displays the word Total in the leftmost cell and the total number of records in the rightmost cell.

To add other calculations to the Total Row:

  1. In the Total row, click on the cell where the calculation is to appear to open a drop-down arrow.
  2. Click the drop-down list arrow to open the menu of options
  3. Click on the desired calculation in the menu to add it the cell.

Formulas that can be added to the Total row are not limited to the calculations in the menu. Formulas can be added manually to any cell in the total row.

Delete a Table, But Save the Data

If you decide you don't need the table, you can delete it without damaging the data is contains:

  1. Click anywhere in the table.
  2. Click on the Design tab of the ribbon.
  3. Click Convert to Range in the Tools group to open a confirmation box for removing the table.
  4. Click Yes to confirm.

The table features such as the drop-down menus and sizing handle are removed, but the data, row shading, and other formatting features are retained.