Group, filter and total large lists of Excel data

/
Excel Logo.png

If you are working with a large list of data, or even if your list isn’t so large, you can group related items together as a Table in Excel (Tables were called Lists back in Excel 2003). Once grouped, Excel enables a small collection of features designed to make managing data easier.

 

Insert a Table

1 Create Table.png
  1. In the Excel workbook, select the cells you want to include in the Table.
  2. On the Insert ribbon, click Table.
  3. If your list has a header row, ensure the My table has headers checkbox is checked.
  4. Click OK.

Features of an Excel Table

Every Excel List has 5 prominent features: AutoFilter, Table Data, Column Headers, Calculated Columns, and the Total Row, which is disabled by default.

2 Elements of a Table.gif
  1. AutoFilter: You can use AutoFilter to filter or sort your Table.
  2. Table Data: Table Data refers to all information contained within the selected range at the time you created the Table.
  3. Column Headers: Every column in a Table must have a name. If you checked "My table has headers," then the first row of data in your selection became column headers. Otherwise, each column inherited a name like "Column 1."
  4. Calculated Columns: Excel automatically copies a formula entered into a cell to all other cells in the column.
  5. Total Row: When this row is enabled, you can select from several predefined functions to calculate results for a given column.

Note: The Total Row functions work in combination with AutoFilter, so that only visible items are included in the calculation results.

It's worth mentioning that when a cell in the Table is active, a Table Tools ribbon appears. Click the Table Tools ribbon to see available options, including an option to enable the Total Row.

Benefits of using Excel Tables

There are several benefits you get by using Excel Tables;

  1. The formatting of the last row is automatically carried forward when you add a new row. (Just press Tab at the end of the last row.)
  2. Any formulas contained in the last row are automatically copied to new rows you add.
  3. Charts and PivotTables that refer to Tables are “aware” when you add or delete rows and adjust the range they refer to automatically.
  4. When sorting, Excel will prevent you from leaving part of your list unsorted.

Convert a Table back to a normal range

  1. Select a cell in the Table.
  2. On the Table Tools ribbon, click Convert to Range.
  3. Click Yes.