How to Sort Your Related Data in Excel With Tables

This article explains how to sort related data with tables in Excel 2019, 2016, 2013, 2010, and Excel for Mac.

Insert a Table

Before you create a table, enter the data in the worksheet. 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 in Excel:

1. Select a single cell inside the block of data.

2. Select Insert.

3. Select Table. Excel selects the entire block of contiguous data and opens the Create Table dialog box.

   If Excel has problems correctly formatting the table, highlight the data before selecting the Insert Table option.

4. If your data has a heading row, check the My table has headers option.

5. Select OK to create the table.

Table Features

Formatting a block of data as a table makes it easy to carry out a variety of tasks on the data without affecting other data in the worksheet. These tasks include sorting data, filtering data, quick calculations, adding column totals, and visually formatting the table.

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

• The drop-down menus in the column headings that contain sort, filter, and search options.
• The alternate shaded rows that make it easier to read the data.
• The sizing handles located in the corners of the table.
• The Quick Analysis icon (in Excel 2013 and newer) that appears in the bottom right corner of the active cell when two or more cells in the table are selected. This makes it easy to analyze the data using charts, pivot tables, running totals, and conditional formatting.

Manage 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 or descending order, by font, or by cell background. You can also define a custom sort order. Additionally, the filter options in the menus allow you to:

• Show only the data that meets the criteria specified.
• Filter by the font or by the cell background color.
• Search for specific records by matching individual fields of 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 resize the table, drag the sizing handle up, down, to the left, or to the right.

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.

Total Row Feature

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.

1. Select a cell in the table.

2. Select the Table Tools Design tab.

3. In the Table Style Options group, place a check in the Total Row checkbox.

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, select the cell where the calculation is to appear. A drop-down arrow appears on the right side of the cell.

2. Select the drop-down arrow to open the menu of options.

3. Choose the desired calculation in the menu to add it to 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 for your data, you can delete it without damaging its contents using the following steps:

1. Select a cell in the table.

2. Select the Table Tools Design tab.

3. In the Tools group, select Convert to Range to open a confirmation box for removing the table.

4. Select Yes to confirm.

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