Table Definition and Features in Excel

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

In general, a table in Excel is a series of rows and columns in a worksheet that contains related data. In versions prior to 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 (a similar option is located on the Home tab).

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 - enter one formula that is automatically applied to every cell in that 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.

Before Inserting a Table

Although it is possible to create an empty table, it is usually 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 in the Tables group) - Excel will select the entire block of contiguous data and open 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 - to change the shading use the Table Styles option located on the Insert tab;
  • the sizing handle located in the bottom right-hand corner of the table making it easy to add or remove records from the table;
  • the Quick Analysis icon (added in Excel 2013) appears in the bottom right-hand corner of the active cell when two or more cells in the table are selected. It makes it easy to analysis the data using:
    • charts;
    • pivot tables;
    • running totals - appear after the last row of table data;
    • conditional formatting.

Managing Table Data

Sorting and Filtering Options   

The sort/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 option in the menus allows you to

  • show only the data that meets the criteria specified, or you can
  • filter by font or cell background color;
  • search for specific records by matching individual fields of data.

Adding and Removing 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.

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 quickly remove it from all other cells.

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.

In addition, 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. Click on the Total Row check box to select it (located in the Table Style Options group);

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 as shown in the image above.

To add other calculations to the Total Row:

  1. In the total row, click on the cell where the calculation is to appear a total - a drop down arrow appears;
  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;

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

Delete a Table, But Save the Data

  1. Click anywhere in the table;
  2. Click on the Design tab of the ribbon
  3. Click Convert to Range (located in the Tools group) - opens 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.