Sort Key Definition and Function

What is a Sort Key and when would I use it in Excel and Google Spreadsheets

Setting Multiple Sort Keys in the Excel Sort Dialog Box
Setting the Sort Key in the Sort Dialog Box. © Ted French

The sort key is the data in the column or columns you want to sort by. It is identified by the column heading or field name. In the image above, the possible sort keys are Student ID, Name, Age, Program, and Month Started

In a quick sort, clicking on a single cell in the column containing the sort key is sufficient to tell Excel what the sort key is.

In multi-column sorts, the sort keys are identified by selecting the column headings in the Sort dialog box.

Sorting by Rows and Sort Keys

When sorting by rows, which involves reordering the columns of data in a selected range, field names are not used. Instead, possible sort keys are identified by row number - such as Row 1, Row 2, etc.

It is important to note that Excel numbers the rows according to their location in the entire worksheet, and not just in the selected data range.

Row 7 may be the first row in the range selected for the sort, but it is still identified as Row 7 in the Sort dialog box.

Sort Keys and Missing Field Names

As mentioned, Excel normally uses the column heading or field names to identify possible sort keys, as shown in the image above.

If a data range does not include field names, Excel uses the column letters for those columns included in the sort range - such as Column A, Column B, etc.

How Multiple Sort Keys Work

Excel's custom sort feature permits sorting on multiple columns by defining multiple sort keys.

In multi-column sorts, the sort keys are identified by selecting the column headings in the Sort dialog box.

If there are duplicate fields of data in the column containing the first sort key - for example, the two students named A. Wilson in the image above, a second sort key - such as Age - can be defined and the records containing the duplicate fields of data will be sorted on this second sort key.

Note: Only records with duplicate fields for the first sort key are sorted using the second sort key. All other records, including those containing duplicate data fields in non-sort key fields - such as the students W. Russell and M. James both being enrolled in the nursing program -  are not affected by the second sort key.

If there are duplicate data fields under the second sort key - for example, if both students name A. Wilson were the same age, a third sort key can be defined to resolve the situation.

As with a quick sort, the sort keys are defined by identifying the columns headings or field names, in the table containing the sort key.